CODEWITHSUNDEEP
sqlselectgreatest-n-per-group
gaffcz
gaffcz

Reputation: 3479

SQL - choose minimal row

How can I get these lines (the same lines with minimal operation, and it's not always 2):

enter image description here

from this?

enter image description here

And that's my select:

select SCR.order_number, SCR.product_code, REL.operation, REL.quantity
from scrDATA as SCR INNER JOIN 
     relBDE as REL ON SCR.order_number = REL.order_number

I need to count a pieces for order_number and product_code (e.g. 1500 pcs for order_number FA36001607 and 5 pcs for order_number FP7201127)

Upvotes: 0

Views: 106

Answers (3)

Arnaud F.
Arnaud F.

Reputation: 8452

Something like:

select SCR.order_number, SCR.product_code, REL.operation, SUM(REL.quantity)
FROM scrDATA as SCR
INNER JOIN relBDE as REL ON SCR.order_number = REL.order_number,
( SELECT SCR2.order_number, MIN(REL2.operation) AS operation
                 FROM scrDATA as SCR2
                 INNER JOIN relBDE as REL2 ON SCR2.order_number = REL2.order_number
                 GROUP BY SCR2.order_number) AS SCR_OP 
WHERE SCR.order_number = SCR_OP.order_number 
  AND SCR.operation = SCR_OP.operation
GROUP BY SCR.order_number, SCR.product_code, REL.operation

Upvotes: 1

alf
alf

Reputation: 18530

I think this is the query you need:

select SCR.order_number, SCR.product_code, REL.operation, REL.quantity
from scrDATA as SCR 
INNER JOIN relBDE as REL ON SCR.order_number = REL.order_number
where REL.operation = 
    (select MIN(REL.operation)
    from scrDATA as SCR 
    INNER JOIN relBDE as REL ON SCR.order_number = REL.order_number)

Upvotes: 1

Eric Petroelje
Eric Petroelje

Reputation: 60498

Seems like this would work:

select SCR.order_number, SCR.product_code, MIN(REL.operation), REL.quantity
from scrDATA as SCR INNER JOIN 
     relBDE as REL ON SCR.order_number = REL.order_number
GROUP BY SCR.order_number, SCR.product_code, REL.quantity

Upvotes: 1

Related Questions