Selecting last 2 entries for each group in a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Selecting last 2 entries for each group in a table

Author

Topic

Xavier
Starting Member

2 Posts

Posted - 2010-06-02 : 16:53:55

Hi
I'm new in the forums, I found this site while googling for any hints regarding this problem I have but couldn't find any information so I hope any of you guys could give me a hand with this.

The thing is, I have a table with thousands of records, I need to get the last 2 most recent entries for every category of data. For example

Table name: Clients

Id | Name | City | Date
1 John NYC 02/20/2009
2 Paul NYC 03/12/2009
3 Stu LA 05/11/2009
4 Michelle SF 04/02/2009
5 Tom NYC 01/01/2008
6 Mary SF 02/02/2008
7 Dan LA 04/09/2008
8 Alex LA 07/02/2009
9 Lisa SF 05/01/2010

So after the query I'll get the next results:

Id | City
1 NYC
2 NYC
3 LA
8 LA
4 SF
9 SF

I only need the id and the group column, in this case City.
I can get the last entry easy with something like this

select max(id) as Id, City from clients group by City order by City

But I need both, the last entry and the previous one, how can I do this?

Thanks in advance :D

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-02 : 16:57:49

This maybe?

select Id,City
from
(
select row_number() over(partition by City order by Date desc) as seq, * from Clients
)t
where t.seq <=2

Xavier
Starting Member

2 Posts

Posted - 2010-06-02 : 17:08:20

quote:

Originally posted by vijayisonly

This maybe?

select Id,City
from
(
select row_number() over(partition by City order by Date desc) as seq, * from Clients
)t
where t.seq <=2

Tried that. It worked. Thanks a lot.

May I ask what does this do?

select row_number() over(partition by City order by Date desc) as seq, * from Clients

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-02 : 17:12:22

Np.

ROW_NUMBER() is a ranking function that was introduced from SQL Server 2005. You can read about them here or at BOL.
http://msdn.microsoft.com/en-us/library/ms189798.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources