CODEWITHSUNDEEP
sql
Gautam
Gautam

Reputation: 1740

SQL get column value associated with max()

I have the following table

ID     Version         
---    ----------    
123      1           
124      2           
125      3           
126      4           
127      5           
128      6

Now I need to get ID value where version# is maximum

What I can do is 

select ID from tbl where version = (select max(version) from tbl)

I don't want to use this since i need to use this part in a join inside another query and i don't want to complicate things further.

Upvotes: 1

Views: 1035

Answers (2)

user330315
user330315

Reputation:

You mentioned you need this in a join, so something like this should do it 

select *
from table_1 as t1
  join (
      select id, 
             row_number() over (order by version desc) as rn
      from table_2
  ) as t2 on t1.id = t2.id and t2.rn = 1

(This is ANSI SQL as you didn't mention a DBMS - but should work on most modern DBMS)

Upvotes: 0

CloudyMarble
CloudyMarble

Reputation: 37566

You can use select FIRST():

SELECT FIRST(id) FROM tbl ORDER BY Version DESC

Or limit the number results using LIMIT 1 option:

SELECT id FROM tbl ORDER BY Version DESC LIMIT 1

Upvotes: 1

Related Questions