Last Work Order Used for Each Rate

[dleewms]

I am using SQL Server 2005. I am attempting to find the last work order that was used for each rate category. I can find the date last used for a specific rate category, but am not able to get the ONE work_no associated with that date. The code below is still yielding multiple work orders for each category. Can anyone point me in the right direction? Thanks!

SELECT TOP (100) PERCENT dbo.rate_cat.rate_cat,
dbo.rate_cat.description,
max(dbo.[work].create_time) AS LastUseDate,
q.work_no
FROM dbo.[work]
LEFT OUTER JOIN dbo.rate_cat
ON dbo.[work].rate_cat = dbo.rate_cat.rate_cat
LEFT OUTER JOIN dbo.business
ON dbo.[work].bill_to_business_id = dbo.business.business_id
LEFT OUTER JOIN
(Select max(work_no) as work_no, create_time
From dbo.[work]
Group by create_time)q
ON work.create_time = q.create_time
and work.work_no = q.work_no
Group by dbo.rate_cat.rate_cat, dbo.rate_cat.description, q.work_no
ORDER BY dbo.rate_cat.rate_cat

 

[gmmastros]

Can you show some sample data and expected results?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dleewms]

Sure. Sorry about that.

Work_No Rate_Cat Description Create_time

2359 10001 Platinum 2011-06-01
2364 10001 Platinum 2011-06-05
2375 10002 Diamond 2011-06-07
2378 10003 Gold 2011-06-08
2383 10002 Diamond 2011-06-10

I would like to see
10001 Platinum 2011-06-05 2364
10002 Diamond 2011-06-10 2383
10003 Gold 2011-06-08 2378

 

[markros]

SQL Server 2005+ solution:

;with cte as (select *, row_number() over (partition by Rate_Catm Description order by Create_Time Desc) as Row from DataTable)

select * from cte where Row = 1

For more details about this and other solutions, check these blogs:

an Aggregated Column's Related Values
an Aggregated Column's Related Values - Part 2



PluralSight Learning Library