mardi 13 octobre 2015

SQL Server : group by with corresponding row values

I need to write a T-SQL group by query for a table with multiple dates and seq columns:

DROP TABLE #temp
CREATE TABLE #temp(
       id char(1),
       dt DateTime,
       seq int)

Insert into #temp values('A','2015-03-31 10:00:00',1)
Insert into #temp values('A','2015-08-31 10:00:00',2)
Insert into #temp values('A','2015-03-31 10:00:00',5)
Insert into #temp values('B','2015-09-01 10:00:00',1)
Insert into #temp values('B','2015-09-01 10:00:00',2)

I want the results to contains only the items A,B with their latest date and the corresponding seq number, like:

id  MaxDate CorrespondentSeq
A   2015-08-31 10:00:00.000 2
B   2015-09-01 10:00:00.000 2

I am trying with (the obviously wrong!):

select id, max(dt) as MaxDate, max(seq) as CorrespondentSeq
from #temp
group by id

which returns:

id  MaxDate CorrespondentSeq
A   2015-08-31 10:00:00.000 5  <-- 5 is wrong
B   2015-09-01 10:00:00.000 2

How can I achieve that?

EDIT

The dt datetime column has duplicated values (exactly same date!)

I am using SQL Server 2005

Aucun commentaire:

Enregistrer un commentaire