Search code examples
sqlgroup-bysnowflake-cloud-data-platformpartitioning

How to set rows with max value within group as true sql

I have a table similar to this:

ID ORDER AGE
12 34 50
99 41 17
12 34 24
99 42 12
12 33 15
12 33 38

I would like to look at the ID column, where if the ORDER value is the max value within the ID group, then to set it as TRUE, and the others as FALSE.

I tried to do


SELECT ID, ORDER,
CASE WHEN ORDER = MAX(ORDER) THEN TRUE ELSE FALSE END AS ACTIVE
FROM MY_TABLE
GROUP BY ID, ORDER;

But that seems to just set every ACTIVE value to true.

Ultimately I am trying to end up with a table like

ID ORDER AGE ACTIVE
12 34 50 TRUE
99 41 17 FALSE
12 34 24 TRUE
99 42 12 TRUE
12 33 15 FALSE
12 33 38 FALSE

What would be the best way to achieve this? Thanks!

Solution

  • Use MAX() window function:

    SELECT ID, ORDER, AGE,
       ORDER = MAX(ORDER) OVER (PARTITION BY ID) AS ACTIVE
FROM MY_TABLE;