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

dategreatest-n-per-grouppostgresql

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.

Best Answer

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

Related Solutions

Ms-access – Minimum value on most recent date per account MS ACCESS SQL

This would be a good place to use window functions if your RDBMS supports them. The example below is T-SQL so if you are not on SQL Server you may have to adapt the syntax and/or keywords.

Window Functions are ANSI Standard starting with SQL:2003, and as such you likely have access to them with any modern RDBMS. If you are on SQL Server read about their implementation of Window Functions here.

SELECT CHACCTBS, CHRQDATE, CHSCORE
FROM
(
    SELECT
         MIN(CHSCORE) OVER(PARTITION BY CHACCTBS) As CHACCTBS
        ,CHRQDATE
        ,CHSCORE
        ,ROW_NUMBER() OVER(PARTITION BY CHACCTBS ORDER BY CHRQDATE DESC, CHSCORE) As Limiter
    FROM RiskScore
) X
WHERE Limiter = 1

The subquery returns your MIN and other columns, and creates a filter by assigning row numbers by your partition and ordered by descending dates. You then take the first row of each partition in the WHERE clause.

Postgresql – How to select a field value in PostgreSQL based to the date computation within the table

Sort the result (descending) by the value of end_date - now() and limit to only one row:

SELECT dollar_value, end_date - now() 
from dollar_rate
order by end_date - now() desc
limit 1;

The computation end_date - now() is actually unnecessary in this case, you can simplify this to:

SELECT dollar_value, end_date - now() 
from dollar_rate
order by end_date desc 
limit 1;

Best Answer

Related Solutions

Ms-access – Minimum value on most recent date per account MS ACCESS SQL

Postgresql – How to select a field value in PostgreSQL based to the date computation within the table

Related Question