Select distinct row with max date from SQL Server table?

Question

I need to get a set of distinct records for a table along with the max date across all the duplciates.

ex:

Select distinct a,b,c, Max(OrderDate) as maxDate
From ABC
Group By a,b,c

The issue is I get a record back for each different date.

Ex:

aaa, bbb, ccc, Jan 1 2009
aaa, bbb, ccc, Jan 28 2009

How can I limit this so I end up with only:

aaa, bbb, ccc Jan 28 2009

I assume the issue is the gorup by and distinct not getting along well.

EDIT: Found the issue that was causing the problem, query results were as expected, not as above.

Answer 1

Something is wrong either with your query or with your example results, as what you describe shouldn't be possible. How about some actual SQL and actual results?

In any event, you don't need distinct there since all you're selecting are your three grouped columns an an aggregate, so you'll by definition end up with all distinct rows. I've never tried this, so perhaps there is some misbehavior when using both of those. Have you tried removing the distinct? What caused you to put it there?

Answer 2

WITH q AS (
        SELECT  abc.*, ROW_NUMBER() OVER (PARTITION BY a, b, c ORDER BY orderDate DESC) AS rn
        FROM    abc
        )
SELECT  *
FROM    q
WHERE   rn = 1

Having an index on (a, b, c, orderDate) (in this order) will greatly improve this query.

Answer 3

If you run this query:

select 'ab' as Col1, 'bc' as col2, 'cd' as col3, getdate() as Date
into #temp
insert into #temp
values ('ab','bc','cd','1/15/09')
insert into #temp
values ('aa','bb','cc','1/1/09')
insert into #temp
values ('aa','bb','cc','1/22/09')

select col1,col2,col3,max(date)
from #temp
group by col1,col2,col3

You should get back:

aa, bb, cc, 2009-01-22 00:00:00.000
ab, bc, cd, 2009-04-30 09:23:07.090

Your query will work as well so something is really wrong or you have not properly communicated the exact nature of your code.