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

Edit your question and provide sample data and desired results *in the question* in tabular format. — Gordon Linoff, Jun 24 '17 at 19:45
Please read http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557 and the accepted answer — , Jun 24 '17 at 20:42

peterm · Accepted Answer · 2017-06-24T21:37:32.527

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

score 0 · Answer 2 · answered Jun 25 '17 at 05:21

Using ROW_NUMBER() after taking distinct records will eliminate the chance of getting the lowest date instead of second lowest if there are duplicate records.

select ID,Deadline 
    from (
        select ID,
        Deadline, 
        ROW_NUMBER() over(partition by ID order by Deadline) RowNum 
        from (select distinct ID, Deadline from SourceTable) T
        ) Tbl
        where RowNum = 2

selecting set of second lowest values

2 Answers2