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 |
My Answer:
You can achieve this by using a subquery to select the first row in each group. Here's an example query:
sql
SELECT id, customer, total
FROM (
SELECT id, customer, total,
ROW_NUMBER() OVER (PARTITION BY customer ORDER BY id) as rn
FROM purchases
) t
WHERE rn = 1;
This query uses the ROW_NUMBER() window function to assign a row number to each row within a group defined by the customer column. The PARTITION BY clause specifies the column to group by (in this case, customer) and the ORDER BY clause specifies the column to order the rows within each group (in this case, id). The outer query then filters out only the rows where the row number is 1, which corresponds to the first row in each group.
Rate this post
3 of 5 based on 8042 votesComments