Help with query

[pwomack]

I have the following data in a table:

order # OpCode Date
12345 50 5/17/09
12345 51 6/12/09
12345 52 6/27/09

I want to write a query that returns only 1 row; the row with the maximum OpCode for an order #. So, I want to return OpCode 52 with Date 6/27/09.

I'll have the value of the order # for the query.

Thanks.

 

[bborissov]

SELECT YourTable.*
FROM YourTable
INNER JOIN (SELECT Order, MAX(OpCode) AS OpCode
                   FROM YourTable
            GROUP BY Order) MaxOrd
     ON YourTable.Order  = MaxOrd.Order AND
        YourTable.OpCode = MaxOrd.OpCode

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[pwomack]

Thanks, Borislav.

 

[markros]

SQL Server 2005 and up

select Order, OptCode, Date from (select *, row_number() over (partition by Order order by Date DESC) as RowNumber from myTable) OrderedSet where RowNumber = 1