title: SQL - min() gets the lowest value, max() the highest, what if I want the 2nd (or 5th or nth) lowest value?
body: <p>The problem I'm trying to solve is that I have a table like this:</p>
  
  <p>a and b refer to point on a different table. distance is the distance between the points.</p>
  
  <pre><code>| 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    |
  ....
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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 &gt; dist;
  delete from mytable where delete = 1;
  </code></pre>
  
  <p>Which would give me a result table like this:</p>
  
  <pre><code>| id | a_id | b_id | distance | delete |
  | 1  |  1   |   1  |  1       |   0    |
  | 5  |  2   |   2  |  0.45    |   0    |
  ....
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>I'm using PostgeSQL.</p>