I want to apply conditional logic based on whether or not my item_no
is the highest in its group.
Sample Data:
group_id item_no
oiegown 1
oiegown 2
oiegown 3
wefwefw 1
wefwefw 2
My Approach:
CASE WHEN (
SELECT MAX(item_no)
FROM my_table
GROUP BY group_id
) = item_no
THEN /*logic here*/
My subquery, as desired, retrieves the highest item_no
per group.
However, the query does not work as I get the Scalar subquery produced more than one element
error. How can I work around this?
CodePudding user response:
Your approach corrected (correlate the subquery to get the maximum for the group ID of the current row only):
CASE WHEN (
SELECT MAX(item_no)
FROM my_table m
WHERE m.group_id = my_table.group_id
) = item_no
THEN /*logic here*/
The alternative with a window function:
CASE WHEN MAX(item_no) OVER (PARTITION BY group_id) = item_no
THEN /*logic here*/