postgres - select with "using"

Question

I found a very useful delete query that will delete duplicates based on specific columns:

DELETE FROM table USING table alias 
  WHERE table.field1 = alias.field1 AND table.field2 = alias.field2 AND
    table.max_field < alias.max_field

How to delete duplicate entries?

However, is there an equivalent SELECT query that will allow to filter the same way? Was trying USING but no success.

Thank you.

score 0 · Answer 1 · answered Jun 23 '17 at 02:56

You can join your table with itself using the specific columns, field1 and field2, and then filter based on a comparison between max_field on both tables.

select t1.*
from mytable t1
  join mytable t2 on (t1.field1 = t2.field1 and t1.field2 = t2.field2)
where t1.max_field < t2.max_field;

You will get all the duplicates whose max_field is not the greatest.

sqlfiddle here.

postgres - select with "using"

1 Answers1