Query on using Max and group by functions.Plz help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query on using Max and group by functions.Plz help

Author

Topic

josh_sql
Starting Member

2 Posts

Posted - 2012-08-31 : 03:41:21

You have three fields ID, Date and Total. Your table contains multiple rows for the same day which is valid data however for reporting purpose you need to show only one row per day. The row with the highest ID per day should be returned the rest should be hidden from users (not returned).

ID Date Total
1 2012-08-25 15
1 2012 -08-25 19
1 2012-08-27 25
3 2012-08-25 30
3 2012 -08-29 29
5 2012-08-25 25

Output:
ID Date Total

5 2012-08-25 25
1 2012-08-27 25
3 2012 -08-29 29

I tried using this query

SELECT MAX(ID), Date FROM tblSales
GROUP BY Date

but I also need the Total column to be displayed. Please let me know how this can be done. Thanks in advance.

Josh

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-31 : 03:53:42

[code]
select *
from
(
select ID, Date, Total, rn = row_number() over (partition by Date order by ID desc)
from tblSales
) d
where rn = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]