Are there any known methods (or tricks) of fetching unique combinations of fields from a table?

For example, there are fields of the form: Field1, Field2, ... FieldN Is it possible to somehow select only unique combinations of the values ​​of these fields? Ie if:

Field1 = A Field2 = B FieldN = C

then:

 Field1 = B Field2 = C FieldN = A

be considered identical to the previous one and discard.

  • 2
    What dialect SQL. It’s possible to come up with something, but on different DBMS requests will be completely different. I think it is necessary to expand the table in a vertical, and to compare sets. For this, the ELT MySQL function may be useful, for example - Mike

1 answer 1

Try this in Postgresql:

 SELECT DISTINCT a.* FROM tbl a WHERE ARRAY[a.fld1, a.fld2, ... a.fldn] = (SELECT MIN(ARRAY[b.fld1, b.fld2, ... b.fldn]) FROM tbl b WHERE ARRAY[a.fld1, a.fld2, ... a.fldn] <@ ARRAY[b.fld1, b.fld2, ... b.fldn] AND ARRAY[a.fld1, a.fld2, ... a.fldn] @> ARRAY[b.fld1, b.fld2, ... b.fldn]);

I do not know if it will work.