SQL Query Group By Help

Question

Hi,

I have the following data:

cust  subject  date
Cust1 Subject1 2010-12-31 21:59:59.000
Cust1 Subject1 2010-12-31 21:59:59.000
Cust4 Subject3 2011-02-27 21:59:59.000
Cust5 Subject1 2010-10-31 21:59:59.000
Cust5 Subject2 2010-10-31 21:59:59.000
Cust6 Subject2 2010-10-31 21:59:59.000
Cust6 Subject2 2010-12-31 21:59:59.000

I need to achieve the following:

• Group them by cust and subject
• If there are 2 records with the same cust and subject then i need to return the one with the highest date
• Following the above, if the dates are the same then return only 1 of them.

The result of the query should be:

cust  subject  date
Cust1 Subject1 2010-12-31 21:59:59.000
Cust4 Subject3 2011-02-27 21:59:59.000
Cust5 Subject1 2010-10-31 21:59:59.000
Cust5 Subject2 2010-10-31 21:59:59.000
Cust6 Subject2 2010-12-31 21:59:59.000

Can anyone help me with this?

I managed to do 2 of the requirements but not all 3.

Answer 1

Use ROW_NUMBER() - if you've not used this and the other partitioning functions previously then I can definitely recommend looking into them as they (especially ROW_NUMBER()) have a great deal of uses.

SELECT cust, subject, date
FROM (
  SELECT cust, subject, date, ROW_NUMBER() OVER (PARTITION BY cust, subject ORDER BY date DESC) AS RN
  FROM <your table>
) SubQuery
WHERE SubQuery.RN = 1

Answer 2

SELECT cust, subject, max([date]) FROM myTable GROUP BY cust, subject;

You don't really have a column called date, do you? date is a reserved word which is why it had to be surrounded by square braces in my query above.

Answer 3

select cust, subject, max(date) from table group by cust, subject