select-over-and-partition.md

Given a stupid table:

# SELECT * FROM sample;
 id | foo | bar 
----+-----+-----
 10 | a   | a
 11 | a   | b
 12 | a   | b
 13 | a   | c
 14 | b   | a
 15 | b   | a
(6 rows)

This is what we can do to aggregate and count how many rows have foo = bar:

# SELECT DISTINCT foo, bar, COUNT(id) OVER(PARTITION BY foo, bar) AS qty FROM sample;
 foo | bar | qty 
-----+-----+-----
 a   | a   |   1
 a   | b   |   2
 b   | a   |   2
 a   | c   |   1
(4 rows)

With that we can just filter out the rest:

# SELECT * FROM 
    (SELECT DISTINCT foo, bar, COUNT(id) OVER(PARTITION BY foo, bar) AS qty FROM sample) dups
  WHERE dups.qty > 1;
 foo | bar | qty 
-----+-----+-----
 a   | b   |   2
 b   | a   |   2
(2 rows)

pedro/select-over-and-partition.md