Alternate approach for getting Minimum of Column B that falls under Max of Column A

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 839
declare @a table(col1 datetime, col2 datetime)
insert into @a select
'20120520', '20120620' union all select
'20120520', '20120513' union all select
'20120530', '20120620' union all select
'20120530', '20120515' union all select
'20120630', '20120515' union all select
'20120630', '20120510' union all select
'20120630', '20120525'
--First approach
SELECT *
FROM (
SELECT *, RN = ROW_NUMBER() OVER (ORDER BY col1 DESC, col2)
FROM @a
)a
WHERE RN = 1;

--Second Approach
select col1, Col2 = min(Col2)
from @a t
where t.col1 = (select max(col1) from @a)
group by col1

/* OUTPUT
col1 Col2
2012-06-30 00:00:00.000 2012-05-10 00:00:00.000
*/

Comments or Responses

Login to post response