Choosing max of date from duplicates

Question

Suppose we have multiple records of the following form:

A B C date1 A B C date 2 D E F date3 D E F date 4

I want to only select the distinct records where the date is the largest. So if date2 is the largest, chose that ABC record. Similarly, if date3 is the largest, choose that DEF record.

What database are you using? What are the table and column names? What have you tried to get the results? — clinomaniac, Feb 15 '18 at 16:30
Possible duplicate of [SQL select only rows with max value on a column](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) — clinomaniac, Feb 15 '18 at 16:31

score 0 · Answer 1 · answered Feb 15 '18 at 16:41

Easiest way is probably something like this;

SELECT A, B, C, Date
FROM table t1
WHERE Date = (SELECT MAX(Date) FROM table t2 WHERE t2.A = t1.A AND t2.B = t1.B AND t2.C = t1.C)

Cleaner would be to make a CTE (or subquery), to get the max date for each combination of other columns, then join back to that from the table

Choosing max of date from duplicates

1 Answers1