SQL: Select first row in each a GROUP BY group?

Question

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY.

Specifically, if I've got a "purchases" table that looks like this:

> SELECT * FROM purchases:
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 made by each customer. Something like this:

> SELECT FIRST(id), customer, FIRST(total)
. FROM purchases
. GROUP BY customer
. ORDER BY total DESC;
FIRST(id) | customer | FIRST(total)
        1 | Joe      | 5
        2 | Sally    | 3

Answer 1

Select Top 1 .....

Answer 2

On Oracle 8i+/SQL Server 2005+/PostgreSQL 8.4+:

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

Supported by any database:

But you need to add logic to break ties:

  SELECT MIN(x.id),  -- change to MAX if you want the highest
         x.customer, 
         x.total
    FROM PURCHASES x
    JOIN (SELECT p.customer,
                 MAX(total) AS max_total
            FROM PURCHASES p
        GROUP BY p.customer) y ON y.customer = x.customer
                              AND y.max_total = x.total
GROUP BY x.customer, x.total