Search code examples
sqlsql-servert-sqldistinct

Select distinct row with max date from SQL Server table?

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.

Solution

  • 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?