Finding Value Of One Column Associated With The Max Value Of Another Column
Feb 4, 2008
I've got a rowset (intermediate result in a large query) that's equivalent to this table:
create table T (
K bigint,
V bigint,
W tinyint,
primary key(K,W)
)
What I want is "for each K, find the V associated with the largest W". Or,
select K,V from T t1 where W = (select max(W) from T t2 where t2.K = t1.K)
What I'd like is a solution that does not involve a self-join on the table (which the sub-query effectively is). The rowset can be sorted by (K asc, W desc), in which case I'm looking for the first (K,V) pair for each unique value of K.
In the larger query, T could have 100's of thousands of rows, and as I mentioned, T is the result of another query on my base tables. I can express T as a CTE easily enough, but the resulting query plan (using the sub-select, as above) ends up performing the query that generated T repeatedly in order to implement the self join.
I can save T into a temp table - in fact, that's what I'm doing now - but I'd rather avoid using a temp table if possible. This query needs to be of the highest possible performance, so if there's a no-temp-table, no-self-join solution, I'm all ears!
I could imagine making a CLR UDF that does the filtering that I want, but I'm not convinced that it'd be a good choice - any insights on whether that's worth trying would be appreciated as well.
View 9 Replies
ADVERTISEMENT
May 2, 2008
Hi,
How to find if a column exists in Database Table ?
thanx in advance
View 6 Replies
View Related
May 19, 2008
Hi All,
How to find all the tables of a database containing a column . for example,how list all the tables of employee database having employeeid ?
Cheers,
Mathi
India.
View 6 Replies
View Related
Apr 30, 2007
I want to find columns name associated with view. For example; can you please help me to write query? I need to write for several tables.
TableName Column View
---------- -------- -------
Employee EmpId v_EmployeeDetails
Employee Firstname v_EmployeeDetails
Employee Lastname --
Employee SSN v_PersonalD
Employee DOB --
___________________________________________________
--just example
go
create view v_EmployeeDetails
as
select EmpId,Firstname from Employee
go
go
create view v_PersonalD
as
select SSN from Employee
View 5 Replies
View Related
Jul 23, 2005
Hi all,How do I find all tables containing a column (say a column includingthe string 'value')?ThanksBruno
View 2 Replies
View Related
Jul 20, 2005
I have a task to where I need to move a column from one table toanother. I want to be sure I update any view, stored procedure,trigger, etc. that references the column. I simply want a query thatwill report the related objects and then I will update them manuallybut before I go and try and figure out how to do this by querying thesys tables is there an sp_sproc that will do this?
View 1 Replies
View Related
Jan 13, 2008
I'm using SQL 2005 Server Management Studio -
I go to the Northwind database - new query window and paste this code, I just found on this forum, to find the Identity column:SELECT C.name AS Colname, UPPER(U.name) AS Coltype, C.status AS Cstatus, C.ColId, C.Id AS Cid, C.length, C.xprec, C.xscale FROM syscolumns C JOIN sys.tables O ON C.id = O.object_id JOIN systypes U ON C.xusertype = U.xusertype WHERE O.name = 'Fred' AND C.status = 128 ORDER BY C.colid SELECT C.name AS Colname, UPPER(U.name) AS Coltype, C.status AS Cstatus, C.ColId, C.Id AS Cid, C.length, C.xprec, C.xscale FROM syscolumns C JOIN sys.tables O ON C.id = O.object_id JOIN systypes U ON C.xusertype = U.xusertype WHERE O.name = 'Products' AND C.status = 128 ORDER BY C.colid
However, I get an error message saying:Invalid object name 'sys.tables'.
What might I be missing here?
View 3 Replies
View Related
Jan 7, 2014
Aim – Find the latest #Account.[Last_Post_Date] for the Fdmsaccountno and populate the latest date for that Parentid. In a new column Called “Parent Last Post Date"
Current results
FdmsaccountnoParentidLast_Post_Date
87802012188487823334288920140105
87802012788187823334288920140103
87802012888987823334288920131231
87870180988087823334288920131217
87802012088687823334288920131204
87823334288987823334288920131201
87870180888287823334288920131130
87802011588687823334288920131120
87875705088487823334288920131011
Desired results
FdmsaccountnoParentidLast_Post_DateParent Last Post Date
8780201218848782333428892014010520140105
8780201278818782333428892014010320140105
8780201288898782333428892013123120140105
8787018098808782333428892013121720140105
8780201208868782333428892013120420140105
8782333428898782333428892013120120140105
8787018088828782333428892013113020140105
8780201158868782333428892013112020140105
8787570508848782333428892013101120140105
My query is
select
#Account.Fdmsaccountno,
#Account.Parentid,
#Account.[Last_Post_Date],
from #Account
inner join [Dim_Outlet] on #account.FDMSAccountNo = [Dim_Outlet].FDMSAccountNo
where #Account.Parentid = '878233342889'
order by [Last_Post_Date] desc
View 3 Replies
View Related
Jul 23, 2005
Hi,I'm having trouble running the following query:select * from message where text_body like ' ----------%'ie, five spaces followed by at least ten hyphens. The query doesn'tcomplete, so eventually I cancel it. If I remove the hyphens from thequery ("... like ' %'") then it runs fine (though it doesn't findthe correct data).Am I confusing SQL Server by using a wildcard or regular expression?(I'm using SQL Server 2000 - 8.99.760).Thanks in advance for any helpRichard
View 5 Replies
View Related
Aug 12, 2015
Query :-
SELECT
MAX(TIME_VAL) AS Duration1 ,
MIN(TIME_VAL) AS Duration2 ,
-- VALUE OF LATITUDE CORRSPONDING TO MAX(TIME_VAL)
-- VALUE OF LATITUDE CORRSPONDING TO MIN(TIME_VAL)
(Select LONGITUDE from (select LONGITUDE , row_number()
[Code] ....
I am not getting Correct values for StartLongitude , EndLongitude. How can i achieve this to get the val of latitude corssponding to max/ min of time_val column.
View 3 Replies
View Related
Nov 21, 2007
Hi
I always seem to come back to dataset issues. Nonetheless, here is my problem:
I am retrieving data using a stored procedure that lists the number of bookings per hour and the revenue that generates also per hour. So, you enter a single date in the stored procedure, for example 2001-10-01 and it will bring back 24 rows corresponding to the 24 hours (obviously) and two columns, one displaying the number of bookings and one displaying how much revenue.
Pretty simple so far.
I have a report using SQL Reporting Services that allows users to enter two dates and therefore you get two tables with the relevant dates data. For example, table one has date 1's data and table 2 has as you'd expect date 2's data. Both tables use different datasets.
The reason why I am using different tables and different datasets is that the stored procedure I use only allows the use of one date at a time therefore, I use a second dataset to generate the second date's data.
The problem occurs when I want to calculate a difference between the two date's data, for example have another table that shows the differrence between table one and table two for bookings for each corresponding hour.
Anyone have any ideas as to how I could achieve this?
View 1 Replies
View Related
Feb 13, 2008
I have a table. In that table I have a list by student number that lists the entry dates into a particular grade. When trying to list only the first time entered, there is no unique way to identify one row from another other than the date. Is there a way to use max or min to only pull one date per student number? I have done a series of case when statements and I am able to get it down to 1 to 2 entries per student number, but I need to get it down to only 1 date per student number.
Thank you for your help
SELECT DISTINCT mx.stu_num,
CASE WHEN er.STU_NUM = ep.STU_NUM
THEN er.enterdater
ELSE CASE WHEN ea.STU_NUM = ep.STU_NUM
THEN ea.enterdatea
ELSE CASE WHEN eb.STU_NUM = ep.STU_NUM
THEN ep.enterdatep
ELSE eb.enterdateb
END
END
END AS entrydate
FROM dbo.mx AS mx LEFT OUTER JOIN
dbo.v_EntryDate9_R AS er ON mx.stu_num = er.stu_num LEFT OUTER JOIN
dbo.v_EntryDate9_P AS ep ON mx.stu_num = ep.stu_num LEFT OUTER JOIN
dbo.v_EntryDate9_A AS ea ON mx.stu_num = ea.stu_num LEFT OUTER JOIN
dbo.v_EntryDate9_B AS eb ON mx.stu_num = eb.stu_num
View 1 Replies
View Related
Jan 28, 2008
I am using Access 2007 and I have 10 columns of data.
I am trying to select all the data from all 10 columns where the first column's data returns only the Unique values and the other columns return all the data from the row that is returned from the first column.
I have used this
SELECT DISTINCT [SFR Rates All].Sorter, [SFR Rates All].SProgram, [SFR Rates All].S_Price, [SFR Rates All].Min_Loan, [SFR Rates All].Max_Loan
FROM [SFR Rates All];
But that returns all the data in the table and all values in the Sorter column with duplicates because each row has distinct data.
I am trying to return unique values from [SFR Rates All].Sorter (the first column) and all the data from the other 9 columns that are contain the row with the unique value in Sorter.
I know that I am missing something basic but well, I can't figure it out.
Thanks
Bob
View 4 Replies
View Related
Nov 18, 2005
I have a table that stores part numbers and manufactuers. Somehow this table has become corrupt showing different manufacturers with the same part numbers.
I know this will take a bit of manual digging to fix, but I want to find a way to pull all rows that have the same part number that have different manufacturers, or just pull up any "duplicate" part numbers and I can determine what is right or wrong as far as the manufactuers and make those changes.
I have tried this, but it does not seem to want to work.
Code:
Select * from my_table
Where partnumber = (select partnumber from my_table) and compName <> (select compName from my_table)
I have tried other variations of the same, but nothing seems to want to show me just the items that have the same part numnbers and different manufacturers. I do not care if there are duplicates of the same part number/manufacturer entries, just if the part number is duplicated where the manufacturers are not the same.
These are the rows I want to edit and group by part number. I have almost a million rows of entries and this is not something I want to go through row by row. :-P
Any ideas?
View 2 Replies
View Related
May 1, 2015
I am trying to find a way where I can search for a column that is associated in all tables of the database. I have created a query but is not executing correctly.
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t8
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%Status%'
ORDER BY schema_name, table_name;
View 3 Replies
View Related
Nov 2, 2015
I have a split string function that will take a comma delimited string and give back a table with all the values.I have a table that has a column with a comma delimited comma delimited list of states.
I can use a where clause to find one state in the table (such as all records that have CA in the states string).But need to find out how to find all the rows that have all or any of the states from a comma delimited parameter.Here is the schema
CREATE FUNCTION [dbo].[split] (@list nvarchar(MAX))
RETURNS @tbl TABLE (svar nvarchar(10) NOT NULL) AS
BEGIN
DECLARE @pos int,
@nextpos int,
@valuelen int
[code]....
View 9 Replies
View Related
Jul 23, 2005
Could any suggest to me a good way to programmatically identify which SPsupdate a database column. I would like to create a cross reference for ourdatabase.
View 5 Replies
View Related
Dec 19, 2006
Example data
CA1000
CA10001
CA10002
CA10003
CA11597
CA11603
CA1001
CA998
CA999
As you can see, CA11603 is the largest number in this list.
When I try the follow sql code,
SELECT
MAX([MyCode])
FROM
[MyTable]
WHERE (SUBSTRING([MyCode], 1, 2) = 'CA')
The largest number comes back as CA997
When I try
MAX([MyCode])
FROM
[MyTable]
WHERE [MyCode] LIKE 'CA%'
The largest number comes back as CA997
SELECT
TOP 1 (SchoolMasterCode)
FROM
SchoolMaster
WHERE (SUBSTRING(SchoolMasterCode, 1, 2) = 'CA') ORDER BY Schoolmastercode
The largest comes back as CA10001
When I try....
SELECT
TOP 1 (SchoolMasterCode)
FROM
SchoolMaster
WHERE (SUBSTRING(SchoolMasterCode, 1, 2) = 'CA')
The largest comes back as CA1278
What am I doing wrong?
View 3 Replies
View Related
Feb 23, 1999
Does anyone have a TSQL utility (that they can share) that recursively searchs sysobjects for a matching input parameter string(for instance column name) for stored procedure object properties that returns the stored procedure name?
View 1 Replies
View Related
Jul 28, 2015
I have a excel file which has a column called "Code" and their values are A,B,C,D,E,F,G,H. I want to create a new column called "status" based on the values of "Code".
Code:
A
B
C
D
E
F
G
H
If A,C,E,G then "status" = "Active" else if B,D,F,H then "Status" = "Inactive". I like to do it using "Derived Column".
View 4 Replies
View Related
Aug 3, 2015
How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?
View 5 Replies
View Related
Oct 16, 2006
Hi all,
The requirement is to have a table say 'child_table', with an Identity column to refer another column from a table say 'Parent_table'..
i cannot implement this constraint, it throws the error when i execute the below Alter query,
ALTER TABLE child_table ADD CONSTRAINT fk_1_ct FOREIGN KEY (child_id)
REFERENCES parent_table (parent_id) ON DELETE CASCADE
the error thrown is :
Failed to execute alter table query: 'ALTER TABLE child_table ADD CONSTRAINT
fk_1_ct FOREIGN KEY (child_id) REFERENCES parent_table (parent_id) ON DELETE
CASCADE '. Message: java.sql.SQLException: Cascading foreign key 'fk_1_ct' cannot be
created where the referencing column 'child_table.child_id' is an identity column.
any workarounds for this ?
View 3 Replies
View Related
Jul 6, 2015
getting result as given below.
Input:
id Name Data
101 AA PQ102BBRAJAKIRANBUBLU
102 BB RS101AAEROJUCHALABAGUNDI
103 CC TU104GGANDICANKILLANYONE
OUTPUT:
id Name Data
101 AA 101AAEROJUCHALABAGUNDI
102 BB PQ102BBRAJAKIRANBUBLU
103 CC
View 4 Replies
View Related
Sep 7, 2007
Hi guys,
If I have a temporary table called #CTE
With the columns
[Account]
[Name]
[RowID Table Level]
[RowID Data Level]
and I need to change the column type for the columns:
[RowID Table Level]
[RowID Data Level]
to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time.
What will be the right syntax using SQL SERVER 2000?
I am trying to solve the question in the link below:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1
Thanks in advance,
Aldo.
I have tried the code below, but getting syntax error...
ALTER TABLE #CTE
ALTER COLUMN
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
I have also tried:
ALTER TABLE #CTE
MODIFY
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
View 18 Replies
View Related
Apr 16, 2007
Good Morning,
Am I new at this so please bear with me. I searched the site and found threads on identifying the meaning of an error code and have instituted that scripting. It is telling me that I have a 'The data value cannot be converted for reasons other than sign mismatch or data overflow.' error. It identifies the colum as 5301.
I searched the threads and found one telling me I could do an advanced editor search on the lineage id to find the column being referenced. I have traced the entire process using this information and cannot find a reference lineage id of 5301. Was that thread information accurate, and if so what do I do now? If not, can someone tell me how the heck I find column 5301? I cannot load my table from a flat file because of this.
Work so far:
I have checked for integrity between column definitions and source flat file. I applied derived column changes to make the data transform to the appropriate data type/order where necessary. This part works without error. (Or seems to, there is no error output from this piece.) It is only on the final attempt to load that the process errors with these messages.
Thank you in advance to anyone who can help me.
Rog
View 7 Replies
View Related
Jul 11, 2007
When you use "Alter Table add Column", it adds the column to the end of the list of fields.
How do you insert the new column to position number 2 for instance given that you may have more than 2 columns?
Create table T1 ( a varchar(20), b varchar(20), c varchar(20))
Alter table add column x varchar(20)
so that the resulting table is
T1 a varchar(20), x varchar(20), b varchar(20), c varchar(20)
Can this be done programmatically?
View 33 Replies
View Related
Mar 7, 2008
Hi I’m trying to alter a table and delete a column I get the following error. The object 'DF__Morningst__LastU__19EB91BA' is dependent on column 'LastUpdated'.
ALTER TABLE DROP COLUMN LastUpdated failed because one or more objects access this column. I tried deleting the concerned constraint. But the next time I get the same error with a different constraint name. I want to find out if I can dynamically check the constraint name and delete it and then drop the column. Can anyone help.IF EXISTS(SELECT 1FROM sysobjects,syscolumnsWHERE sysobjects.id = syscolumns.idAND sysobjects.name = TablenameAND syscolumns.name = column name)BEGIN EXECUTE ('ALTER TABLE tablename DROP CONSTRAINT DF__SecurityM__DsegL__08C105B8')EXECUTE ('ALTER TABLE tablenameDrop column columnname)ENDGO
View 1 Replies
View Related
Nov 12, 2012
I am trying to exclude records from a table where the ID column is the same but the Mail code Column is multi-valued.For Example: (the table looks like....)
ID Mail_code
111111 XNT
111111 N11
111111 XNC
222222 XNC
222222 XNL
333333 XNC
So, if there is any ID that has a value of XNC, I want to exclude the ID all together from my output regardless of the other values.
View 3 Replies
View Related
Feb 26, 2015
Is there a way to display a column alias as part of the result set column labels?
View 9 Replies
View Related
Mar 5, 2007
Hi,
I have dates in "mmddyy" format coming from the sources and they are older dates of mid 80s like 082580 for instance.
When I cast it this way (DT_DBTIMESTAMP) Source_Date , It says ok but throws a runtime error.
When I hardcode a date in same format, (DT_DBTIMESTAMP) "082580" , It becomes red (an indication of syntax error) . Please note that we use double quotes in expressions in Derived Column Transformation; So an anticipation that using double quotes over single ones would be the syntax problem would be wrong.
Any help in this will sincerely be appreciated.
Thanks
View 7 Replies
View Related
Jun 4, 2015
How to change column size dynamically depends on content of the column.
View 2 Replies
View Related
May 13, 2015
I am creating a report in SSRS which has the following criteria:
- Row 1 (parent) is 'Product'
- Row 2 (child) is 'Feed'
- Columns are date. I have 5 dates showing at any one time across the top. The date field is set up as a parameter so depending on the date the user selects, the report will show that date on the end column and then the 4 days prior to that in the other columns.
- Data is the number of records.
I have a sub total on the Product and the report is collapsed on Product as default.
What i'm stuck on is trying to insert a column at the very end that will show the variance between the last two dates. So the difference between the date the user selected (@date parameter) and the day before that.
View 6 Replies
View Related
Nov 17, 2015
I have a Table Having Date,Opening,Addition,Sale values where opening value comes in the very first row other times it is zero.
In ssrs how can i have a report showing closing value = Opening+Addition-Sale in current row (it is simple for 1st row ). this closing be the opening value in next row and same formula to be continued...
Date Opening AdditionTotalTank saleClosing Stock
01-11-15 14435 0 14435 8243 20627
02-11-15 0 15000 15000 9433
03-11-15 0 9000 9000 9436
04-11-15 0 12000 12000 8392
05-11-15 0 6000 6000 8157
06-11-15 0 12000 12000 8456
07-11-15 0 15000 15000 10903
08-11-15 0 6000 6000 8485
09-11-15 0 6000 6000 9413
10-11-15 0 21000 21000 6413
View 7 Replies
View Related