Help with SQL Select 1

[pwomack]

I have the following data in a table.

select OrderNo, OptKey, JobDate
from OptimizerJob
order by OrderNo, JobDate

OrderNo OptKey JobDate
123456 1414388210 2009-04-07 14:18:40.030
123456 306771073 2009-04-07 14:19:05.280
1234567 1184659209 2009-04-07 14:19:28.500
1234567 1661234550 2009-04-07 14:19:59.373
12345678 621885380 2009-04-07 14:22:23.520
123456789 2102027589 2009-04-07 14:23:01.960
123456789 1232245987 2009-04-07 14:23:28.663
123456789 1035518668 2009-04-07 14:42:24.287

I want to write a Select statement that returns only the order number and optkey for the latest job date.

So, I want this returned:

OrderNo OptKey
123456 306771073
1234567 1661234550
12345678 621885380
123456789 1035518668

Can someone help?

 

[tcsbiz]

All of the records in your example have the same job date. Wouldn't you return all of them?

How would the code know what the job date is? A parameter? Another table?

 

[RiverGuy]

SELECT a.*
FROM OptimizerJob a

INNER JOIN

(SELECT OrderNo, MAX(JobDate) AS MaxDate
FROM
OptimizerJob
GROUP BY OrderNo) b

ON a.OrderNo = b.OrderNo AND a.JobDate = b.MaxDate

 

[pwomack]

Thanks, RiverGuy.

I'm an admirer of your knowledge.

 

[markros]

Or for SQL Server 2005 and up you can do it more "modern" way

select * from (select * from OptimizerJob, row_number() over (partition by OrderNo order by JobDate DESC) as Rn) Ranked where Rn = 1