How to get max status as a column?

November 22, 2021

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?

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
    )

>Solution :

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