Get last record in a group

Get Last/Max/Min Value in a Group

Description

When you have many values per group in your data, sometimes you want to just return the row with the max value. Finding the max will only give you the max value. So to return the entire row we can do:

WITH ranked AS (
   SELECT <COLS>, RANK() OVER (PARTITION BY <GROUP_COL> ORDER BY <ORDER_COL> DESC [ASC]) as ranked_value
   FROM <TABLE>
)
SELECT * FROM ranked
WHERE ranked_value = 1

Example (optional)

with demo_data as (
  select 'green' color, 1 value union all 
  select 'green' color, 5 value union all 
  select 'purple' color, 10 value union all 
  select 'purple' color, -1 value 
),
ranked as (select color, value, row_number() over(partition by color order by value desc) rank_value from demo_data)
select * from ranked where rank_value = 1```

|color|value|rank_value|
|-----|------|---------|
|green|5|1|
|purple|10|1|