CODEWITHSUNDEEP
sqlamazon-redshift
user14263992
user14263992

Reputation: 115

How to retrieve rows that has the maximum row number for a customer?

enter image description here

Highlighted rows has the maximum row number for a customer. How to get output report with only the rows that are highlighted??

select name, order id, date, (ROW_NUMBER () OVER (PARTITION BY name,order id ORDER BY date)) as rn from table;

Upvotes: 0

Views: 41

Answers (1)

nad2000
nad2000

Reputation: 4905

You can use either a subquery or a CTE:

WITH data AS (
  select name, order_id, date, (ROW_NUMBER () OVER (PARTITION BY name,order id 
  ORDER BY date)) as rn from table),
last_entry AS (SELECT name, max(rn) AS rn FROM d GROUP BY name)
SELECT data.*
FROM data JOIN last_entry
  ON last_entry.name = data.name
    AND last_entry.rn = data.rn;

Upvotes: 1

Related Questions