Minimum of column B that falls under maximum of column A

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 226
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;

/ * OUTPUT
col1 col2 RN
2012-06-30 00:00:00.000 2012-05-10 00:00:00.000 1
*/

Comments or Responses

Login to post response