By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,133 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

how to display last record of same ID

P: 58
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
1 Week Ago #1
Share this Question
Share on Google+
2 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,158
Expand|Select|Wrap|Line Numbers
  1. SELECT Max(Revision) AS Latest 
  2. FROM YourTableName 
  3. WHERE DrawingID = 111 AND DrawingNumber = 555555;
Hope this hepps!
1 Week Ago #2

NeoPa
Expert Mod 15k+
P: 31,308
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT [DrawingId]
  2.      , [DrawingNumber]
  3.      , [Revision]
  4. FROM   [YourTable]
  5.        INNER JOIN
  6.        (SELECT   [DrawingId]
  7.                , Max([Revision]) AS [MaxRev]
  8.         FROM     [YourTable]
  9.         GROUP BY [DrawingId]) AS [sQ]
  10.   ON   [YourTable].[DrawingId]=[sQ].[DrawingId]
  11.  AND   [YourTable].[Revision]=[sQ].[MaxRev]
Obviously, if [DrawingId] and [DrawingNumber] are not matched, you'll have to handle those appropriately.
1 Week Ago #3

Post your reply

Sign in to post your reply or Sign up for a free account.