SQL - min() gets the lowest value, max() the highest, what if I want the 2nd (or 5th or nth) lowest value?

Question

The problem I'm trying to solve is that I have a table like this:

a and b refer to point on a different table. distance is the distance between the points.

| id | a_id | b_id | distance | delete |
| 1  |  1   |   1  |  1       |   0    |
| 2  |  1   |   2  |  0.2345  |   0    |
| 3  |  1   |   3  |  100     |   0    |
| 4  |  2   |   1  |  1343.2  |   0    |
| 5  |  2   |   2  |  0.45    |   0    |
| 6  |  2   |   3  |  110     |   0    |
....

The important column I'm looking is a_id. If I wanted to keep the closet b for each a, I could do something like this:

update mytable set delete = 1 from (select a_id, min(distance) as dist from table group by a_id) as x where a_gid = a_gid and distance > dist;
delete from mytable where delete = 1;

Which would give me a result table like this:

| id | a_id | b_id | distance | delete |
| 1  |  1   |   1  |  1       |   0    |
| 5  |  2   |   2  |  0.45    |   0    |
....

i.e. I need one row for each value of a_id, and that row should have the lowest value of distance for each a_id.

However I want to keep the 10 closest points for each a_gid. I could do this with a plpgsql function but I'm curious if there is a more SQL-y way.

min() and max() return the smallest and largest, if there was an aggregate function like nth(), which'd return the nth largest/smallest value then I could do this in similar manner to the above.

I'm using PostgeSQL.

Answer 1

Try this:

SELECT  *
FROM    (
    SELECT  a_id, (
        SELECT b_id
        FROM mytable mib
        WHERE mib.a_id = ma.a_id
        ORDER BY
            dist DESC
        LIMIT 1 OFFSET s
        ) AS b_id
    FROM    (
        SELECT DISTINCT a_id
        FROM mytable mia
        ) ma, generate_series (1, 10) s
    ) ab
WHERE   b_id IS NOT NULL

Checked on PostgreSQL 8.3