Skip to content
Advertisement

SQL: exclude rows that are duplicates according to some column arrangement

In my problem, I have these rows e.g.:

They show pair-wise combinations of ids and names, together with another column c.

I consider row 1+2, and 3+4 as duplicates, considering the pairings of ids and names. Rows 1+2, or 3+4 show basically the same information.

I had no luck removing the duplicates with grouping, because id1 + id2, or name1 + name2 respectively are distinct columns.

Can I add something to my SQL query that removes these ‘duplicates’, so that only rows 1+3 are output?

Advertisement

Answer

One approach here would to be aggregate by the c column and then use a least/greatest trick to tease apart the duplicates. Here is a general solution which should work on any version of SQL:

Here is a working demo.

Note that on databases which have a LEAST and GREATEST function, the above can be simplified to:

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement