MaxOf field & another field as single row

Question

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

Answer 1

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.

Answer 2

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

Much appreciated :)

Thanks