May 18, 2011 at 3:38 pm
I'm having a brain dead day because this is something I can't recall and I know I should.
How can one go about returning 1 or more columns from a table where one of those columns is the Xth Aggregate value in table. For example in the Adventureworks DB lets say I want to return 2 or more columns from the Sales.SalesOrderDetail table where the Row contains the Max(ModifiedDate) for each SalesOrderID?
I know this can be done with some fancy SQL coding by querying the table multiples times with sub-querys and so on but I'm trying to do this and query the table just once or at least as few times as posible and I thought the RAMKING functions could od this but I'm getting that so far.
If you know of a great article or post on the site that deatils how to do this I'd much appreciate it. I don't need the exact answer if there's something I can link over to that does.
Thanks
Kindest Regards,
Just say No to Facebook!May 18, 2011 at 7:14 pm
DECLARE @test-2 TABLE (
SalesOrderId INT,
ModifiedDate DATETIME,
PRIMARY KEY CLUSTERED (SalesOrderID, ModifiedDate));
INSERT INTO @test-2 ( SalesOrderId, ModifiedDate )
VALUES ( 1, '2011-05-19 01:10:52'),
( 1, '2011-05-19 02:10:52'),
( 1, '2011-05-19 03:10:52'),
( 1, '2011-05-19 04:10:52'),
( 1, '2011-05-19 05:10:52'),
( 1, '2011-05-19 06:10:52'); -- this is the row we want
WITH cte AS
(
SELECT SalesOrderId,
ModifiedDate,
RN = ROW_NUMBER() OVER (PARTITION BY SalesOrderId ORDER BY ModifiedDate DESC)
FROM @test-2
)
SELECT *
FROM cte
WHERE RN = 1;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 19, 2011 at 3:28 pm
WayneS (5/18/2011)
DECLARE @test-2 TABLE (SalesOrderId INT,
ModifiedDate DATETIME,
PRIMARY KEY CLUSTERED (SalesOrderID, ModifiedDate));
INSERT INTO @test-2 ( SalesOrderId, ModifiedDate )
VALUES ( 1, '2011-05-19 01:10:52'),
( 1, '2011-05-19 02:10:52'),
( 1, '2011-05-19 03:10:52'),
( 1, '2011-05-19 04:10:52'),
( 1, '2011-05-19 05:10:52'),
( 1, '2011-05-19 06:10:52'); -- this is the row we want
WITH cte AS
(
SELECT SalesOrderId,
ModifiedDate,
RN = ROW_NUMBER() OVER (PARTITION BY SalesOrderId ORDER BY ModifiedDate DESC)
FROM @test-2
)
SELECT *
FROM cte
WHERE RN = 1;
Thanks for the reply along with sample code.
Q - This requires that the row you want can eb sorted so that it is the fir row in the results in the CTE, correct?
Assuming the answer is yes...
I was thinking there was a way to get the RANKING functions to provide AGgergate level results without the limitataions that GROUP by has. In this example the Nth record has to be the first or sorted to be the rifrts to work. I wish I didn't have this NDA so I could post my sample code as it does such a better job of describing the issue then I can in words.
Thanks again.
Kindest Regards,
Just say No to Facebook!May 20, 2011 at 7:41 am
YSLGuru (5/19/2011)
Thanks for the reply along with sample code.
Q - This requires that the row you want can eb sorted so that it is the fir row in the results in the CTE, correct?
Assuming the answer is yes...
I was thinking there was a way to get the RANKING functions to provide AGgergate level results without the limitataions that GROUP by has. In this example the Nth record has to be the first or sorted to be the rifrts to work. I wish I didn't have this NDA so I could post my sample code as it does such a better job of describing the issue then I can in words.
Thanks again.
No Problem - it's frequently easier to answer with code (plus, I can test to ensure that it works).
Yes, this does require that the row that you're looking for be able to be sorted to be the first row (you did ask using a MAX(datefield) as your example...)
There has to be a way that you can provide some sample data to show the problem. Can it be related to anything else (college, baseball... anything) where you can then make up some sample data?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 26, 2011 at 3:16 pm
Wayne,
I managed to work it out and get what I was looking for however I've got another RANKING functions question I was hoping you might shed some light on.
Is there any way to use the Raking functions/process so as to get the equivelant of Count(*) where the value is the aggregate grouped by the whole result set? Lets say I get back 1000 rows, is there a way to get that avlue as one of the values returned in each row?
Using the example you gave in your last post, what would go in the below (where X is) that would return the value that is equal to the total number of rows in the querys reesult?
SELECT SalesOrderId,
ModifiedDate,
RN = ROW_NUMBER() OVER (PARTITION BY SalesOrderId ORDER BY ModifiedDate DESC),
'My Ranking Formula' AS 'x'
FROM @test-2
Thanks
Kindest Regards,
Just say No to Facebook!May 26, 2011 at 3:25 pm
Yes you can;
something like
CountofRows = COUNT(*) OVER(PARTITION BY YourGroupingcolumn)
May 26, 2011 at 3:37 pm
I just stumbled across how to get the total row count returned as a value in the results. Another user (cold Coffe) posted the suggestion to go with
Count(*) OVER (PARTITION BY GroupingFiled)
But the problem with that is what item to tuse as teh Grouping field since there was none in which every row has the same value and so the value this returns will not reflect the numebr of rows returned. Thats when it hit me to do this:
Count(*) OVER(PARTITION BY 1)
When that worked I tried this variation
Count(TABLE.PrimaryKey) OVER( PARTITION BY 1)
And it too worked.
Thanks again to all replies, this has been a big help.
Kindest Regards,
Just say No to Facebook!May 26, 2011 at 3:45 pm
Awesome.. happy to be of some help 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply