Scalar subquery producing more than 1 record with partition – SQL

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:

2

Actual Output:

2
2

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

>Solution :

Use an aggregate function along with GROUP BY instead 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;