Scalar subquery producing more than 1 record with partition

I have data with two rows as follows:

group_id    item_no
weoifne     1
weoifne     2

I want to retrieve the max item_no for each group_id. I'm using this query:

SELECT MAX(item_no)
  OVER (PARTITION BY group_id)
FROM my_table;

I need only one record because I'm embedding this query in a CASE WHEN statement to apply logic based on whether or not item_no is the highest value per group.

Desired Output:

Actual Output:

2
2

How do I modify my query to only output one record with the maximum item_no per group_id?

CodePudding user response：

Use an aggregate function along with GROUP BY instead of an window function.

A window function, also known as an analytic function, computes values over a group of rows and returns a single result for each row. This is different from an aggregate function, which returns a single result for a group of rows.

https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls

SELECT group_id, MAX(item_no)
  FROM my_table
 GROUP BY group_id;

CodePudding user response：

If you still want to use the window function, you can use DISTINCT in your script to get rid of the duplicates as shown below. DISTINCT works across all the columns

SELECT  DISTINCT    group_id
                    , MAX(item_no) OVER (PARTITION BY group_id)
FROM                my_table