Select the last 3 records per id

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 2000 Forums

Transact-SQL (2000)

Select the last 3 records per id

Author

Topic

qjam
Starting Member

19 Posts

Posted - 2006-11-22 : 22:57:08

Hi all,

I need help creating a sql statement which will only return the last three records per ID. Basically, I have a table which has:

EmployeeNum
DateofInvoice
Cost

And I need to show the last three invoices per employee.

Any help would be fantastic,

Thanks!

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-11-22 : 23:59:35

this can be done with a corelated subquery. There may be better ways - I am not as much of a sql god as some of the regulars on this forum

Here I am assuming you have an int pk column, I named it "id":


declare @t table (id int, EmployeeNum int, DateofInvoice datetime, Cost int)

insert into @t 
select  1, 1, '2006-01-01', 12 union all
select  2, 1, '2006-02-01', 13 union all
select  3, 1, '2006-03-01', 14 union all
select  4, 1, '2006-04-01', 15 union all
select  5, 1, '2006-05-01', 16 union all
select  6, 2, '2006-06-01', 17 union all
select  7, 2, '2006-07-01', 18 union all
select  8, 2, '2006-08-01', 19 union all
select  9, 2, '2006-09-01', 20

select * from @t tt where id in 
(
	select top 3 id from @t 
	where EmployeeNum = tt.EmployeeNum 
	order by DateofInvoice desc
)

SqlSpec - a fast, cheap, and comprehensive data dictionary generator for
SQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-23 : 00:00:56

You need to define the criteria for what the last three invoices are for each ID.

Also, I don't even see a column called ID in your table.

CODO ERGO SUM

qjam
Starting Member

19 Posts

Posted - 2006-11-23 : 20:30:54

Hi,

Sorry, I should have said Employee Number is the Primary Key (what I've said is ID). The problem is that this query needs to be able to pick up the last 3 invoices, regardless of what date the invoice was, eg.

One employee could have an invoice for 2006-10-30, 2006-10-12 and 2006-10-01, and another could have one for 2006-10-30, 2006-10-12, and 2006-02-01.

Any ideas? I like the top clause and the order by date of invoice desc, I think we're on the right track.

qjam
Starting Member

19 Posts

Posted - 2006-11-23 : 20:48:16

Ah, I think I have found a solution:

Select I1.EmployeeNum, I1.DateOfInvoice, I1.Cost
From Invoice I1
Where I1.DateOfInvoice in (
Select top 3 I2.DateOfInvoice
from Invoice I2
Where I2.EmployeeNum = I1.EmployeeNum
Order by DateOfInvoice DESC
)
Order by I1.EmployeeNum

Thanks so much for your help!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-23 : 22:55:23

Also refer point 2
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources