PostgreSQL – Get Rows Based on Minimum Value

greatest-n-per-grouppostgresqlquery

Having this table:

warehouse_id	destination_id	days
1	1	2
1	1	3
2	1	3
2	1	4
3	1	5
3	2	5
1	2	2
2	2	3

I'd like to get the warehouse_id and days for each value of warehouse_id, where the row has the minimum value of days, and destination_id matches a specific value, ordered by days.

For example for destination_id = 1

warehouse_id	days
1	2
2	3
3	5

Best Answer

Here is the query which orders by first for warehouse_id and days then gets first record only according to DISTINCT ON clause

select distinct on (warehouse_id) 
    warehouse_id,
    days 
  from this_table 
  where destination_id = 1 
  order by warehouse_id, days

warehouse_id | days
-----------: | ---:
           1 |    2
           2 |    3
           3 |    5

db<>fiddle here

Related Solutions

PostgreSQL 9.3 – Get Top-K Minimum Per Range

Another way, using the LATERAL syntax, available from 9.3+ versions::

WITH t AS
  ( SELECT f1, f2,  
           MAX(f2) OVER (PARTITION BY f1) AS range_to
    FROM dbTable 
    GROUP BY f1, f2
  ) 
SELECT t.f1, 
       -- t.f2 AS range_from, t.range_to,
       t.f2 || ' - ' || t.range_to AS RNG,
       x.f3
FROM t
  CROSS JOIN LATERAL
    ( SELECT f3 
      FROM dbTable 
      WHERE f1 = t.f1 AND f2 >= t.f2 
      ORDER BY f3 LIMIT 2
    ) AS x (f3)
ORDER BY t.f1, t.f2, x.f3 ;

Test at SQLfiddle.

Or without the CTE:

SELECT t.f1, 
       -- t.f2 AS range_from, 
       -- MAX(t.f2) OVER (PARTITION BY t.f1) AS range_to,
       t.f2 || ' - ' || MAX(t.f2) OVER (PARTITION BY t.f1) AS RNG,
       x.f3
FROM dbTable AS t
  CROSS JOIN LATERAL
    ( SELECT f3 
      FROM dbTable 
      WHERE f1 = t.f1 AND f2 >= t.f2 
      ORDER BY f3 LIMIT 2
    ) AS x (f3)
GROUP BY t.f1, t.f2, x.f3
ORDER BY t.f1, t.f2, x.f3 ;

Select to get a value from a id combined with a max value

greatest-n-per-group problems are typically solved with a window function:

select idAction, numberLine, action
from (
  select idAction, numberLine, action, 
         row_number() over (partition by idaction order by numberline desc) as rn 
  from the_table
) t
where rn = 1;

If you can have more then one maximum value for numberline the above will only return one of them. If you want to see all of them, use dense_rank() instead of row_number()

Best Answer

Related Solutions

PostgreSQL 9.3 – Get Top-K Minimum Per Range

Select to get a value from a id combined with a max value

Related Question