getting the row corresponding to the max of other row [duplicate]

Question

I have a table TAB having two fields A and B, A is a Varchar2(50) and B is a Date.

Supposing we have these values:

 A  |      B
------------------
 a1 | 01-01-2013
 a2 | 05-05-2013
 a3 | 06-06-2013
 a4 | 04-04-2013

we need to have the value of field A corresponding to the maximum of field B, that is mean that we need to return a3.

I made this request:

select A 
from TAB 
where 
B = (select max(B) from TAB)

but I want to avoid nested select like in this solution.

Have you an idea about the solution ?

Thank you

Answer 1

I made an sqlfiddle where I listed 4 different ways to achieve what you want. Note, that I added another row to your example. So you have two rows with the maximum date. See the difference between the queries? Manoj's way will give you just one row, although 2 rows match the criteria. You can click on "View execution plan" to see the difference how SQL Server handles these queries.

The 4 different ways (written in standard SQL, they should work with every RDBMS):

select A 
from TAB 
where 
B = (select max(B) from TAB);


select top 1 * from tab order by b desc;


select 
*
from
tab t1
left join tab t2 on t1.b < t2.b
where t2.b is null;


select
*
from
tab t1
inner join (
  select max(b) as b from tab
  ) t2 on t1.b = t2.b;

and here two more ways especially for SQL Server thanks to a_horse_with_no_name:

select *
from (
  select a, 
         b,
         rank() over (order by b desc) as rnk  
  from tab
) t
where rnk = 1;

select *
from (
  select a,
         b, 
         max(b) over () as max_b
  from tab
) t
where b = max_b;

See them working here.

Answer 2

You can try this way also

SELECT TOP 1 A FROM TAB ORDER BY B DESC

Thanks

Manoj