Select first row in each GROUP BY group?
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 (total) 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 :
On Oracle 8i+, 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 = 1Supported 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
http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group