Find most recent record for each item

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

General SQL Server Forums

New to SQL Server Programming

Find most recent record for each item

Author

Topic

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2014-10-29 : 12:45:36

We have a work order notes table in our ERP system, and I want to see the most recent note record for each work order. Sometimes there will one be one, so I want to see all those, but sometimes there will be several notes for each work order and in this case I want to see only the most recently added note for that work order.

The query below shows some results as an example. In this case I want to see all the records except for work order number DN-000023 where I only want to see the note dated/timed 07-12-2011 16:52 (the most recent one).

select id, worknumber, date, notes from worksordernotes

id worknumber date
----------- ------------ ----------------------- --------------------
1 DN-000056 2011-12-07 13:22:00 13.20 PM JAMES- SPOK
2 DN-000079 2011-12-07 14:24:00 JCB HAVE TOLD ME THE
4 DN-000065 2011-12-07 15:48:00 ANDY FROM SITE RANG
5 DN-000023 2011-12-07 15:54:00 CHASED THIS 4 TIMES
6 DN-000023 2011-12-07 16:52:00 HOLTS ATTENDED THIS
7 DN-000092 2011-12-08 09:50:00 RETURNING WITH PARTS

Many thanks
Martyn

AASC
Starting Member

24 Posts

Posted - 2014-10-29 : 12:58:48

I guess this is what you are trying

create table #worksordernotes
(
ID int,
worknumber varchar(50),
[date] datetime,
notes varchar(200)
)

insert into #worksordernotes
select 1, 'DN-000056', '2011-12-07 13:22:00', 'JAMES- SPOK'
union all
select 2, 'DN-000079' ,'2011-12-07 14:24:00' ,'JCB HAVE TOLD ME THE'
union all
select 4, 'DN-000065' ,'2011-12-07 15:48:00' ,'ANDY FROM SITE RANG'
union all
select 5, 'DN-000023' ,'2011-12-07 15:54:00' ,'CHASED THIS 4 TIMES'
union all
select 6, 'DN-000023' ,'2011-12-07 16:52:00' ,'HOLTS ATTENDED THIS'
union all
select 7, 'DN-000092', '2011-12-08 09:50:00' ,'RETURNING WITH PARTS'

select ID,worknumber,[date],notes
from
(
select ROW_NUMBER()over(partition by worknumber order by [date] desc) OID,*
from #worksordernotes
)orders where orders.OID=1
order by id

drop table #worksordernotes

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2014-10-29 : 13:12:49

Yes that is exactly it.

Many thanks for your help!

Martyn

AASC
Starting Member

24 Posts

Posted - 2014-10-29 : 13:22:10

Hurray wembleybear, good luck

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources