1

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
Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
vicocas
  • 115
  • 1
  • 6
  • 1
    the minimum value of days MIN() for destination_id = 1 WHERE for each value of warehouse_id GROUP BY – Akina Mar 01 '21 at 05:01
  • 1
    The best solution depends on undisclosed cardinalities. See: https://stackoverflow.com/a/7630564/939860, https://dba.stackexchange.com/a/177174/3684 – Erwin Brandstetter Mar 01 '21 at 17:41

2 Answers2

3

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

Sahap Asci
  • 2,889
  • 1
  • 14
  • 26
0

A simple aggregation like

select warehouse_id, min(days)
from this_table
where destination_id = 1
group by warehouse_id;

gives the answer in the question.

Colin 't Hart
  • 9,323
  • 15
  • 35
  • 43