CODEWITHSUNDEEP
sqlpostgresql
user2641077
user2641077

Reputation: 159

How to select the first row that met condition

I have the following View in PostgreSQL:

idshipment idorder  quantity_order  date   quantity_in_shipment percent_sent
 50          1        1020         1.1.16    432                    42
 51          1        1020         17.1.16   299                    71
 51          1        1020         20.1.16   144                    85
 51          1        1020         45.1.16   145                    100
 52          2         1           3.1.17    5                      100

This View shows shipments per order. For example:

idorder=1 was sent by 4 shipments:

quantity in first shipment is 432 which means 42% of order was sent

quantity in second shipment is 299 which means 71% of order was sent

quantity in third shipment is 144 which means 85% of order was sent

quantity in forth shipment is 145 which means 100% of order was sent

I need a query which will show me the first date where each order was sent above 75%. meaning each order shows only one row. For the above data I should see:

idorder date
1       20.1.16  (cause its 85% first time above 75%)
2       3.1.17   (cause its 100% first time above 75%)

How can i do that?

Upvotes: 1

Views: 2444

Answers (3)

OLIVER.KOO
OLIVER.KOO

Reputation: 5993

use group by to get only one record per idorder and MIN() to aggregate date by selecting the earliest date

I created a table call shipment that has data like you provided: and execute this query

SELECT s.idorder, MIN(s.date) as date
FROM shipment s
WHERE percent_sent >= 75
GROUP BY s.idorder

result:

idorder     date
----------- ----------
1           2016-01-20
2           2017-03-01

Upvotes: 0

Usagi Miyamoto
Usagi Miyamoto

Reputation: 6289

Try something like this:

SELECT iorder, MIN("date") AS "date"
  FROM your_view
  WHERE percent_sent >= 75
  GROUP BY iorder

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can use distinct on:

select distinct on (t.idshipment) t.*
from t
where t.percent_sent >= 75
order by t.idshipment, t.percent_sent asc;

Upvotes: 3

Related Questions