Select first row in each 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;

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

Answer 1

You can use the below code which is valid for versions like:

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

Are you interested in learning SQl from scratch! Have a look at this interesting video on SQL provided by Intellipaat:

Query:

WITH summary AS (

    SELECT purchase.id, 

           purchase.customer, 

           purchase.total, 

           ROW_NUMBER() OVER(PARTITION BY purchase.customer 

                                 ORDER BY purchase.total DESC) AS rk

      FROM PURCHASES purchase)

SELECT s1.*

  FROM summary s1

 WHERE s1.rk = 1

Refer to this informative video about ORDER BY. 

Below code is supported by any database. But you must add logic to break ties:

SELECT MIN(x1.id),  -- change to MAX if you want the highest

X1.customer, 

 X1.total

FROM PURCHASES x1

JOIN (SELECT purchase.customer,

 MAX(total) AS max_total

FROM PURCHASES purchase

GROUP BY purchase.customer) y ON y.customer = x.customer

 AND y.max_total = x.total

GROUP BY x.customer, x.total