Clever Geek Handbook

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

+3


source to share


5 answers


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

+3


source


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
0


source


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.

0


source 


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

0


source


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
0


source






More articles:


All Articles