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 <- 1 8 | 5 <- 2 7 | 5 # skip, already have sec=5 6 | 4 <- 3 5 | 1 # skip, already have sec=1 4 | 1 # skip, already have sec=1 3 | 3 <- 4 2 | 2 1 | 1
Of course the
SQL algorithm can (will!) be different than I described.
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);