select specified number of unique IDs where second column is unique Look at the following example starting from the top row (id=9) and work your way down, selecting a limit of 4 rows that have sec's that we have not yet seen. We "select" id=9 because we don't yet have sec=1. We continue to work our way down like this, but when we get to id=7 we skip it because we already have sec=5 (from row with id=8). We continue in the same manner, and we finally stop at id=3 because we have accumulated 4 rows (our desired limit).

 id | sec
----+-----
  9 |   1  &lt;- 1
  8 |   5  &lt;- 2
  7 |   5  # skip, already have sec=5
  6 |   4  &lt;- 3
  5 |   1  # skip, already have sec=1
  4 |   1  # skip, already have sec=1
  3 |   3  &lt;- 4
  2 |   2
  1 |   1


Of course the SQL algorithm can (will!) be different than I described.

Desired result:

 id
----
  9
  8
  6
  3
(4 rows)


If I wanted to increase the limit to 5 rows, then the row with id=2 would be included in the results. However, if I increased the limit to 6 rows, the row with id=1 would not be added because sec=1 has already been seen.

Note: Though it shouldn't matter, I am on PostgreSQL 9.3.1.



In case you want to quickly build the table to test this out:

CREATE TABLE my_table (id serial primary key, sec integer DEFAULT 0 NOT NULL);
INSERT INTO my_table (sec) VALUES
  (1)
, (2)
, (3)
, (1)
, (1)
, (4)
, (5)
, (5)
, (1);
CREATE INDEX index_my_table_on_sec ON my_table (sec);
SELECT id,
       sec
FROM (
  SELECT id,
         sec,
         row_number() OVER (PARTITION BY sec ORDER BY id DESC) AS rn
  FROM my_table
) t
WHERE rn = 1
ORDER BY id DESC 
LIMIT 4;


SQLFiddle example: http://sqlfiddle.com/#!15/1ca01/1