Mani Mani - 6 months ago 9
SQL Question

Pick minimum value and update all the rows SQL

I have a scenario where I need to pick up a minimum value of a priority column and take the product of those and put it in all the columns.

SD PL PRIO PRDT PNAME
1 29 10 MM CAR
1 LI 20 SS BRAKE
1 AA 30 AA ZZZZ


Since the Priority 10 is the minimum of gorup SD 1 MM should be replaced like below.

SD PL PRIO PRDT PNAME
1 29 10 MM CAR
1 LI 20 MM BRAKE
1 AA 30 MM ZZZZ


Could you please help with the select query.

Answer

You can use ROW_NUMBER:

SELECT
    t.SD, t.PL, t.PRIO, t2.PRDT, t.PNAME
FROM YourTable t
INNER JOIN(
    SELECT *,
        ROW_NUMBER() OVER(PARTITION BY SD ORDER BY PRIO) AS rn
    FROM YourTable
) t2
    ON t.SD = t2.SD
WHERE t2.rn = 1

How about using a correlated subquery:

UPDATE YourTable t
    SET PRDT = (
        SELECT PRDT
        FROM YourTable t2
        WHERE 
            t2.SD = t.SD
            AND t2.PRIO = (SELECT MIN(t3.PRIO) FROM YourTable t3 WHERE t3.SD = t.SD)
    )