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
Go to Top of Page

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2014-10-29 : 13:12:49
Yes that is exactly it.

Many thanks for your help!

Martyn
Go to Top of Page

AASC
Starting Member

24 Posts

Posted - 2014-10-29 : 13:22:10
Hurray wembleybear, good luck
Go to Top of Page
   

- Advertisement -