How to select records by nth minimum/maximum value of date column in postgresql?

Question

I have a table as follow:

create table x(id int, work date);  

this table has the following records:

insert into x values(1,'2017-01-01'),(2,'2017-01-01'),(3,'2017-02-02'),(4,'2017-01-15'),(5,'2017-01-15');  

Now, I want to select the records with first, second and third date values.
My desired output would be as follows:
If I want to select record with first date value then the output would be:

id      work  
1     2017-01-01  

If I select record with second date value then the output would be:

id      work  
 4    2017-01-15  

Any help is appreciated in advanced.

Answer 1

Use a window function:

select id, work
from (
  select id, work, row_number() over (order by work) as rn
  from x
) t
where rn = 1 -- or 2 or 3 ...

If you need to deal with duplicate dates, use dense_rank() instead of row_number() and use a second window function to provided numbers for each date:

select id, work
from (
  select id, work, 
         dense_rank() over (order by work) as rn, 
         row_number() over (partition by work order by id) as rn2
  from x
) t
where rn = 2  -- second highest date value 
  and rn2 = 1 -- first date with the second highest date value

Online example: http://rextester.com/VQVTB53094

Answer 2

Another way:

SELECT *
FROM x
WHERE work = (SELECT DISTINCT work FROM x ORDER BY 1 OFFSET 1 LIMIT 1)
ORDER BY id
LIMIT 1;

Or more simpliest:

SELECT DISTINCT on (work)
       work, id
FROM x
ORDER BY 1, 2
OFFSET 1
LIMIT 1;

You can choose num of element by changing offset.