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!
CodePudding user response：
Use MAX() window function:
SELECT ID, ORDER, AGE,
ORDER = MAX(ORDER) OVER (PARTITION BY ID) AS ACTIVE
FROM MY_TABLE;
CodePudding user response：
You will want to rank the rows using row_number. Order by order and partition by id. This will provide you with a rank value where you can set the Active flag where rank = 1.
https://docs.snowflake.com/en/sql-reference/functions/row_number.html