I'm trying to return only the latest works order details for an item of equipment, but my query is returning all the works order records for each FleetNo. Basically, I want to see only the most recent works order, but I need to see it's corresponding service hours and the service name for that works order and no others. Here you can see it is repeating rows as I'm getting something wrong:
Select MAX(wh.itemcode) FleetNo
,MAX(ISNULL(CAST(LEFT(wt.name, charindex(' ', wt.name) -1)as int),0)) [ServiceNo]
,wt.name [ServiceName]
,wh.worknumber
,wh.date_in
from worksorderhdr wh
left join worksordertypes wt on wt.id = wh.worktype
where wh.rejected <> 1 and wh.worktype in (10,11,12,13,14,15,16,17,18,19,20,21,22,32,51,52,53,55,56,64,65)
Group by wh.itemcode, wt.name, wh.worknumber, wh.date_in
order by FleetNo
And results, showing show examples of extra rows for items that I don't want:
FleetNo ServiceNo ServiceName worknumber date_in
00GA0003 1000 1000 Hour Service HU-DN-008275 2015-04-13 15:56:00
00GA0003 250 250 Hour Service IP-DN-116597 2015-05-05 13:30:00
03EX0002 500 500 Hour Service IP-DN-120858 2015-06-09 07:45:00
03EX0003 500 500 Hour Service HU-DN-010433 2015-07-29 13:30:00
03EX0006 500 500 Hour Service IP-DN-120327 2015-06-03 09:30:00
05EX0001 500 500 Hour Service IP-DN-126168 2015-07-21 10:35:00
05MC0001 100 100 Hour Service IP-DN-078879 2014-07-24 11:21:00
05MC0001 500 500 Hour Service IP-DN-108533 2015-03-04 12:29:00
05MC0002 100 100 Hour Service ER-DN-001362 2015-02-18 00:00:00
06MT0001 1500 1500 Hour Service ER-DN-001885 2015-04-01 13:00:00
06MT0002 1000 1000 Hour Service ER-DN-002049 2015-04-13 11:41:00
06MT0002 500 500 Hour Service IP-DN-103415 2015-01-28 16:37:00
06MT0003 1500 1500 Hour Service IP-DN-118844 2015-06-01 16:29:00
06MT0003 1000 1000 Hour Service IP-DN-106136 2015-02-16 13:06:00
So for example for 06MR0003 I only want to see the 1500 service record as it is the most recent. How can I achieve this please?