Sonam Daultani Sonam Daultani - 3 months ago 10
SQL Question

SQL: Get the column value corresponding to maximum column value

I have the following table

Id | S_Id | T_Id |
1 | 1 | abc |
2 | 2 | abc |
3 | 3 | abc |
4 | 4 | abc |
5 | 1 | xyz |
6 | 2 | xyz |
7 | 3 | xyz |


I will have T_Id as input e.g: abc. I want a query to select the Id of the row with maximum S_id i.e. 4

Answer

Use rank() window function if you are on 11g or before

select id
  from (
   select id, rank() over (partition by T_Id order by S_Id desc) as rn
     from myTable
    where T_Id = 'abc') t
where t.rn = 1;

on 12c you can use fetch

select id
  from myTable
 where T_Id = 'abc'
order by S_Id desc
fetch first 1 row;
Comments