How do I aggregate non-aggregate values in GROUP BY queries?

debugcn Published at Dev

zuzak

I have a table full of duplicates. I'm trying to convert them so I can put a unique constraint across two fields (say, identifier1 and identifier2).

I would like to "collapse" those duplicates into single records, but of my records contain differing strings. I'd like to keep the last-touched in these circumstances (keeping the one from the highest ID and discarding the rest).

For example, I can aggregate the startDate below with MIN() -- but how do I only get the most recent location?

    id | identifier1 | identifier2 | location   | startDate
    1  | alice       | 0001        | ambridge   | 2016-01-01
    2  | bob         | 1312        | brigadoon  | 2017-01-01
    3  | alice       | 0001        | brigadoon  | 2017-05-01
    4  | bob         | 9999        | brigadoon  | 2015-01-01
    5  | celeste     | 1234        | cittegazze | 2011-01-01

    id | identifier1 | identifier2 | location   | startDate
    6  | alice       | 0001        | brigadoon  | 2016-01-01
    7  | bob         | 1312        | brigadoon  | 2017-01-01
    8  | bob         | 9999        | brigadoon  | 2015-01-01
    9  | celeste     | 1234        | cittegazze | 2011-01-01

Michał Turczyn

Try this:

select A.identifier1, A.identifier2, A.startDate, B.Location from (
    select identifier1,
           identifier2,
           MIN(startDate) AS startDate
    from TABLE_NAME
    group by identifier1, identifier2
) AS A JOIN TABLE_NAME AS B
ON (A.identifier1 = B.identifier1 and A.identifier2 = B.identifier2 and A.startDate = B.startDate)

Collected from the Internet

Please contact [email protected] to delete if infringement.