MaxOf field & another field as single row

NachitoMax 411 Reputation points
2022-09-06T22:27:21.497+00:00

Hey

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

job_id
unit_id
part_id
version_id
timestamp

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?

Thanks

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
Transact-SQL
Transact-SQL
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
    2022-09-07T02:01:47.89+00:00

    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,
    LiHong


    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
    2022-09-07T02:15:47.903+00:00

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

    Much appreciated :)

    Thanks

    0 comments No comments