August 11, 2015 at 9:41 am
I am trying to pull the top 3 max date values for every account. So, for instance, an account might have multiple dates:
account 1234565 has dates 20150101, 20150215, 20150216, 20150801... so I would want to pull only the 3 top max dates: 20150215, 20150216 and 20150801. I will then do other coding once I get this data for all accounts.
I appreciate your help!
August 11, 2015 at 9:47 am
You can use a code like this:
WITH RowNums AS(
SELECT *, ROW_NUMBER() OVER(PARTITION BY account ORDER BY date DESC) rn
FROM SomeTable
)
SELECT *
FROM RowNums
WHERE rn <= 3;
A different approach could be taken, but it depends on what else you're doing.
August 11, 2015 at 9:49 am
Luis Cazares (8/11/2015)
You can use a code like this:
WITH RowNums AS(
SELECT *, ROW_NUMBER() OVER(PARTITION BY account ORDER BY date DESC) rn
FROM SomeTable
)
SELECT *
FROM RowNums
WHERE rn <= 3;
A different approach could be taken, but it depends on what else you're doing.
Was just about to post the same thing. The only thing I would add is that you should store your dates as date or datetime. The way you posted makes it looks like they are stored as ints which is a serious pain to work with.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 11, 2015 at 9:51 am
Sean Lange (8/11/2015)
Luis Cazares (8/11/2015)
You can use a code like this:
WITH RowNums AS(
SELECT *, ROW_NUMBER() OVER(PARTITION BY account ORDER BY date DESC) rn
FROM SomeTable
)
SELECT *
FROM RowNums
WHERE rn <= 3;
A different approach could be taken, but it depends on what else you're doing.
Was just about to post the same thing. The only thing I would add is that you should store your dates as date or datetime. The way you posted makes it looks like they are stored as ints which is a serious pain to work with.
From experience, I totally agree with that.
August 11, 2015 at 12:47 pm
Quick alternative to Luis's fine solution
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @SAMPLE_DATE TABLE (account INT ,dates DATE);
INSERT INTO @SAMPLE_DATE(account,dates)
VALUES
(1234565, '2015/01/01')
,(1234565, '2015/02/15')
,(1234565, '2015/02/16')
,(1234565, '2015/08/01');
SELECT TOP (3)
SD.account
,SD.dates
FROM @SAMPLE_DATE SD
ORDER BY SD.dates DESC;
Results
account dates
----------- ----------
1234565 2015-08-01
1234565 2015-02-16
1234565 2015-02-15
August 11, 2015 at 12:56 pm
THANK YOU SO MUCH for your quick response.... Looks like this is working...I'm going to do some testing to ensure I am getting what I want.
Maria
August 11, 2015 at 1:20 pm
Not sure which is more efficient?? The table has about 40k rows in it. But I will try both! Thank you again!
August 11, 2015 at 1:23 pm
maria.witkowski (8/11/2015)
Not sure which is more efficient?? The table has about 40k rows in it. But I will try both! Thank you again!
I don't think that Eirikur realized you want the top 3 for each account. His fine example would be super easy if you only wanted the top three from the entire table but I am pretty sure you want the top three for each account which is what the code that Luis posted will do.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 11, 2015 at 1:27 pm
Sean Lange (8/11/2015)
maria.witkowski (8/11/2015)
Not sure which is more efficient?? The table has about 40k rows in it. But I will try both! Thank you again!I don't think that Eirikur realized you want the top 3 for each account. His fine example would be super easy if you only wanted the top three from the entire table but I am pretty sure you want the top three for each account which is what the code that Luis posted will do.
He he, no I didn't, just applied trivial optimization here:-D My bad
😎
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply