How do I select rows with a distinct column value sorted by date?
I have this table:
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)
I want to select rows with separate column2, ordered by column3. This is the desired output:
Col1 Col2 Col3 Col4
AAA SKB 2013-01-15 00:00:00.000 5
AAA SKA 2013-01-31 00:00:00.000 15
AAA SKC 2013-02-01 00:00:00.000 25
How can i do this?
I am using MS SQL 2005 and 2008
try it
;with cte as
(
select *,
row_number() over (partition by column1, column2
order by column3) rnk
from #tmp
) select * from cte where rnk = 1
order by column3;
SQL DEMO HERE
it should do
http://sqlfiddle.com/#!3/d3dad/11
select * from
(
select max(column1) as column1, column2 as column2, max(column3) as column3,
max(column4) as column4
from #tmp
group by column2
) a
order by column3
It depends on what you want. You have to handle other columns.
From this exact data, to get an accurate result:
select column1, column2, min(column3), min(column4)
from #tmp
group by column1, column2
However, generally, the value of column3 can be from a different record than the value of column4, and as written in SQL, if you have more than one value in column1 with the same value in column2, you will get more than one result from the same column2.
SELECT t.column1, t.column2, t.column3, t.column4
FROM @tmp t
INNER JOIN (SELECT DISTINCT column2, MIN(column3) [column3] FROM @tmp GROUP BY column2) v
ON t.column2 = v.column2 AND t.column3 = v.column3
ORDER BY t.column3
This will get unique column2 with oldest column3 and filters the table by sorting the results by column3
I think it's row_number()
better ( Sql-Demo )
select column1, column2, column3, column4
from (
select column1, column2, column3, column4,
row_number() over (partition by column2 order by column3) rn
from #tmp ) A
where rn=1
order by column3