sql - Select first row in each GROUP BY group?

itemprop="text">

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;

My
Output:

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. Something like
this:

SELECT FIRST(id), customer,
            FIRST(total)
FROM purchases
GROUP BY customer
ORDER BY
            total
            DESC;

Expected
Output:

FIRST(id) |
            customer |
            FIRST(total)

----------+----------+-------------
 1 | Joe
            | 5
 2 | Sally | 3

class="post-text" itemprop="text">

class="normal">Answer

On Oracle
9.2+ (not 8i+ as originally stated), SQL Server 2005+, PostgreSQL 8.4+, DB2, Firebird
3.0+, Teradata, Sybase,
Vertica:

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

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