How to get max status as a column?

user_9124458 0
Viewed 75

I want to create a column that shows whether it is the max order_status as TRUE or FALSE based on created_at.

Is there a way to achieve this without a subquery in Snowflake?

enter image description here Here is my example data:

    WITH t1 AS (
SELECT 'A' AS id, 'created' AS status, '2021-05-18 18:30:00'::timestamp AS created_at UNION ALL
SELECT 'A' AS id, 'created' AS status, '2021-05-19 11:30:00'::timestamp AS created_at UNION ALL
SELECT 'A' AS id, 'pending' AS status, '2021-05-19 12:00:00'::timestamp AS created_at UNION ALL
SELECT 'A' AS id, 'successful' AS status, '2021-05-20 18:30:00'::timestamp AS created_at
    )
snowflake-cloud-data-platform window-functions sql
2 Answers

Using windowed MAX:

WITH t1(id, status, created_at) AS (
  SELECT 'A', 'created', '2021-05-18 18:30:00'::timestamp UNION ALL
  SELECT 'A', 'created', '2021-05-19 11:30:00'::timestamp UNION ALL
  SELECT 'A', 'pending', '2021-05-19 12:00:00'::timestamp UNION ALL
  SELECT 'A', 'successful', '2021-05-20 18:30:00'::timestamp AS created_at
)
SELECT *, created_at = MAX(created_at) OVER(PARTITION BY ID) AS is_final_order_status
FROM t1;

Output:

enter image description here

user_50708790

A cased row_number could work

SELECT id, status, created_at
, CASE 
  WHEN 1 = ROW_NUMBER() OVER (PARTITION BY id ORDER BY created_at DESC) 
  THEN 'TRUE' 
  ELSE 'FALSE'
  END is_final_order_status
FROM t1
user_40034190
Related