how to display last record of same ID

[irsmalik]

Hello friends

I have a table having below fields.

DrawingId DrawingNumber Revision
111 555555 0
111 555555 1
111 555555 2
111 555555 3
111 555555 4

I want to display the Last Record through Query. Kindly guide me how to do it.
Thanks
irsmalik

[twinnyfo]

SELECT Max(Revision) AS Latest 
FROM YourTableName 
WHERE DrawingID = 111 AND DrawingNumber = 555555;

Hope this hepps!

[NeoPa]

If we assume that [DrawingId] and [DrawingNumber] are matched (Unfortunately the question is too unclear with the sample data too limited to be sure.) then we'd need something like :

SELECT [DrawingId]
     , [DrawingNumber]
     , [Revision]
FROM   [YourTable]
       INNER JOIN
       (SELECT   [DrawingId]
               , Max([Revision]) AS [MaxRev]
        FROM     [YourTable]
        GROUP BY [DrawingId]) AS [sQ]
  ON   [YourTable].[DrawingId]=[sQ].[DrawingId]
 AND   [YourTable].[Revision]=[sQ].[MaxRev]

Obviously, if [DrawingId] and [DrawingNumber] are not matched, you'll have to handle those appropriately.