CODEWITHSUNDEEP
t-sqlsql-server-2005
spiderman
spiderman

Reputation: 1585

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

Upvotes: 1

Views: 477

Answers (4)

Charles Bretana
Charles Bretana

Reputation: 146499

Select id, dt, seq
From #temp t
where dt = (Select Max(dt) from #temp
            Where id = t.Id)

If there are duplicate rows, then you also need to specify what the query processor should use to determine which of the duplicates to return. Say you want the lowest value of seq,

Then you could write:

Select id, dt, seq
From #temp t
where dt = (Select Max(dt) from #temp
            Where id = t.Id)
   and seq = (Select Min(Seq) from #temp
              where id = t.Id
                 and dt = t.dt)

Upvotes: 0

Mukund
Mukund

Reputation: 1689

SELECT ID, DT, SEQ 
FROM (
        SELECT ID, DT, SEQ, Row_Number()
            OVER (PARTITION BY id ORDER BY dt DESC, seq DESC) AS row_number
        FROM temp            
     ) cte
WHERE row_number = 1;

Demo : http://www.sqlfiddle.com/#!3/3e3d5/5

Upvotes: 1

spiderman
spiderman

Reputation: 1585

With trial and errors maybe I have found a solution, but I'm not completely sure this is correct:

select A.id, B.dt, max(B.seq) 
from (select id, max(dt) as maxDt
      from #temp
      group by id) as A
inner join #temp as B on A.id = B.id AND A.maxDt = B.dt
group by A.id, B.dt

Upvotes: 0

flo
flo

Reputation: 10241

You can use a ranking subselect to get only the highest ranked entries for an id: 

select id, dt, seq 
from (
        select id, dt, seq, rank() over (partition by id order by dt desc, seq desc) as r
        from #temp            
     ) ranked
where r=1;

Upvotes: 1

Related Questions