Select first row in each GROUP BY group (greatest-n-per-group problem)?

Technology CommunityCategory: SQLSelect first row in each GROUP BY group (greatest-n-per-group problem)?
VietMX Staff asked 3 years ago
Problem

Purchases table:

id | customer | total
---+----------+------
 1 | Joe      | 5
 2 | Sally    | 3
 3 | Joe      | 2
 4 | Sally    | 1

I’d like to query for the id of the largest purchase (total) made by each customer.

Consider:

WITH summary AS (
    SELECT p.id, 
           p.customer, 
           p.total, 
           ROW_NUMBER() OVER(PARTITION BY p.customer 
                                 ORDER BY p.total DESC) AS rk
      FROM PURCHASES p)
SELECT s.*
  FROM summary s
 WHERE s.rk = 1