How to get max status as a column?-CodePudding

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
    )

CodePudding user response：

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

CodePudding user response：

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: