Step 1: Just Create a table with name #emp create table #tmp ( column1 varchar(3), column2 varchar(5), column3 datetime, column4 int ) insert into #tmp values ('AAA', 'SKA', '2013-02-01 00:00:00', 10) insert into #tmp values ('AAA', 'SKA', '2013-01-31 00:00:00', 15) insert into #tmp values ('AAA', 'SKB', '2013-01-31 00:00:00', 20) insert into #tmp values ('AAA', 'SKB', '2013-01-15 00:00:00', 5) insert into #tmp values ('AAA', 'SKC', '2013-02-01 00:00:00', 25) Step 2: you partition by column2, the rank function will create ranks for groups of column2 values. There will be different ranks for rows where column2 = 'SKA' than rows where column2 = 'SKB' and so on. Step 3: with cte as ( select *, rank() over (partition by column2 order by column3) rnk from t ) select * from cte where rnk = 1 order by column3; COLUMN1 | COLUMN2 | COLUMN3 |COLUMN4 | RNK ------------------------------------------------------------------------------ AAA | SKB | January, 15 2013 00:00:00+0000 |5 | 1 AAA | SKA | January, 31 2013 00:00:00+0000 |15 | 1 AAA | SKC | February, 01 2013 00:00:00+0000 |25 | 1