Search code examples
sqlgoogle-bigquerysubquerywindow-functions

How to compare max value in each group to current row? - SQL

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?

Solution

  • 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*/