How about:

SELECT *
FROM service_orders
INNER JOIN (SELECT so_num, MAX(revision_num) revision_num FROM
service_orders) max_order_revision
    ON service_orders.so_num = max_order_revision.so_num
            AND service_orders.revision_num 
max_order_revision.revision_num

That's untested...you could also write it as an EXISTS subselect...

HTH,
Darin Strait, MS SQL Server Development and Administration
http://home.earthlink.net/~dstrait/professional/resume.htm


----- Original Message -----
From: "Chellappa, Vasanthy P." <CHELLAVP@m...>
To: "sql language" <sql_language@p...>
Sent: Monday, May 07, 2001 7:51 AM
Subject: [sql_language] Select only the latest service orders


> I have a table of service orders with the following fields:
>
> SO_num, revision_num, amount ......
>
> How can i select only the latest service orders based on the rev number?
>
> eg:
> so rev amount ....
> 1 0      50
> 1 1      20
> 1 2      30
> 2     0      10
> 3     0      10
> 3     1      20
>
> the result should be:
> 1     2      30
> 2     0      10
> 3     1      20
>
> thank you in advance for your help.
>
> vas