CODEWITHSUNDEEP
sqlsql-servert-sql
developer
developer

Reputation: 1697

SQL query to get max value's data with group by

I have the following Table:

Date         Id    Count
2018-09-01   100   50   
2018-09-01   101   60
2018-09-01   102   55
2018-09-02   103   40
2018-09-02   104   30
2018-09-02   105   20
2018-09-02   106   10
2018-09-03   107   30
2018-09-03   108   70

I would like to get rows having max Id for each date and its max Id's count column.

Result table: 

Date         Id    Count
2018-09-01   102   55   
2018-09-02   106   10
2018-09-03   108   70

What should be the sql query to get this result?

Thanks.

Upvotes: 0

Views: 393

Answers (3)

Eray Balkanli
Eray Balkanli

Reputation: 7990

I think using self join would be an alternative:

select t.*
from table t
inner join (select Date, max(t.ID) as DID from table t group by Date) t2
on t.Date = t2.Date and t.ID = t2.DID

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

Use row_number() :

select top (1) with ties t.*
from table t
order by row_number() over (partition by date order by cnt desc);

Upvotes: 8

Gordon Linoff
Gordon Linoff

Reputation: 1270993

You don't want aggregation, you want filtering.

select t.*
from t
where t.count = (select max(t2.count) form t t2 where t2.date = t.date);

Upvotes: 1

Related Questions