MaxOf field & another field as single row

NachitoMax 411 Reputation points


I cant seem to work this out... I have a table-


I need to get the timestamp by the Max of version_id using job_id, unit_id & part_id as filters. something like

SELECT Max(version_id) As MaxOfVersion, timestamp
FROM MyTable
WHERE job_id = 245 AND unit_id = 20 AND part_id = 1

I can do it by calling 2 queries but wondered if i could do it in one query? My 2 queries first get the MaxOfVersion as a single filed then use that to get as a parameter value to get the timestamp. Can it be incorporated into a single query?


SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,607 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,546 questions
0 comments No comments
{count} votes

Accepted answer
  1. CosmogHong-MSFT 22,541 Reputation points Microsoft Vendor

    Hi @NachitoMax
    Since you have a filter using job_id, unit_id & part_id, how about using TOP(1) along with ORDER BY instead of MAX() to get the max version_id. Check this:

    SELECT TOP(1) version_id As MaxOfVersion,timestamp  
    FROM MyTable  
    WHERE job_id = 245 AND unit_id = 20 AND part_id = 1  
    ORDER BY version_id DESC  

    Best regards,

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. NachitoMax 411 Reputation points

    Yes of course, that works. Sometimes the easier things are harder to see

    Much appreciated :)


    0 comments No comments