2

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.

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

1
  • Thanks @a_horse_with_no_name, but if we have two records with same date then the rn=2 will select the second record with the same date. I need to select nth-date-value i.e I want to select first date with rn=1, then second date with rn=2. – Abdul Raheem Ghani Feb 11 '17 at 7:20
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.