selecting set of second lowest values

Question

I have two columns of interest ID and Deadline:

ID    Deadline (DD/MM/YYYY)
1        01/01/2017
1        05/01/2017
1        04/01/2017
2        02/01/2017
2        03/01/2017  
2        06/02/2017
2        08/03/2017

Each ID can have multiple (n) deadlines. I need to select all rows where the Deadline is second lowest for each individual ID.

Desired output:

ID    Deadline (DD/MM/YYYY)
1        04/01/2017
2        03/01/2017  

Selecting minimum can be done by:

select min(deadline) from XXX group by ID

but I am lost with "middle" values. I am using Rpostgresql, but any idea helps as well.

Thanks for your help

Answer 1

One way is to use ROW_NUMBER() window function

SELECT id, deadline
  FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY deadline) rn
      FROM xxx 
  ) q
 WHERE rn = 2 -- get only second lowest ones

or with LATERAL

SELECT t.*
  FROM (
    SELECT DISTINCT id FROM xxx
  ) i JOIN LATERAL (
    SELECT *
      FROM xxx
     WHERE id = i.id
     ORDER BY deadline 
     OFFSET 1 LIMIT 1
  ) t ON (TRUE)

Output:

 id |  deadline
----+------------
  1 | 2017-04-01
  2 | 2017-03-01

Here is a dbfiddle demo