Need to use a column in a Select statement but does not want in Group By clause

Question

I am using this query to fetch certain load_ids:

SELECT 
    ROW_NUMBER() OVER (ORDER BY ldm.load_id) AS rn,
    CASE 
        WHEN pkd.status = 'STAGED' 
            THEN (ldm.load_id + '*') 
            ELSE ldm.load_id 
    END AS load_id
FROM
    t_load_master ldm
INNER JOIN 
    t_pick_detail pkd ON ldm.load_id = pkd.load_id
WHERE
    ldm.wh_id = '270'
    AND ldm.door_loc = 'DDR793'
    AND ldm.status != 'S'
    AND pkd.status IN ('STAGED', 'LOADED')
GROUP BY 
    pkd.load_id, ldm.load_id, pkd.status
HAVING 
    NOT (SUM(pkd.shipped_quantity) >= SUM(pkd.planned_quantity))

In simple words the query returns load_id with asterisk if its grouped status is 'STAGED' and without asterisk if its 'LOADED'.

This is the result I am getting:

As you can see I am getting load_id = '5279380000' twice because it has a few rows in 'LOADED' and a few in 'STAGED' status. I want to have only the one with the asterisk in the results.

Is there any way to accomplish this?

Answer 1

If you want one line per id you need to use an agregrate function for the value of staged. As you want the higher value of staged ('STAGED' comes after 'LOADED' in an alphabetical sort) we can use MAX().

SELECT 
    ROW_NUMBER() OVER (ORDER BY ldm.load_id) AS rn,
    CASE 
        WHEN MAX(pkd.status) = 'STAGED' 
            THEN (ldm.load_id + '*') 
            ELSE ldm.load_id 
    END AS load_id
FROM
    t_load_master ldm
INNER JOIN 
    t_pick_detail pkd ON ldm.load_id = pkd.load_id
WHERE
    ldm.wh_id = '270'
    AND ldm.door_loc = 'DDR793'
    AND ldm.status != 'S'
    AND pkd.status IN ('STAGED', 'LOADED')
GROUP BY 
    pkd.load_id, ldm.load_id, pkd.status
HAVING 
    NOT (SUM(pkd.shipped_quantity) >= SUM(pkd.planned_quantity));