How to select the first row in each GROUP BY group with SQL?

Spread the love

Sometimes, we want to select the first row in each GROUP BY group with SQL.

In this article, we’ll look at how to select the first row in each GROUP BY group with SQL.

How to select the first row in each GROUP BY group with SQL?

To select the first row in each GROUP BY group with SQL, we can use DISTINCT ON.

For instance, we write

SELECT DISTINCT ON (customer)
       id, customer, total
FROM purchases
ORDER BY customer, total DESC, id;

to return the result of the first rows with the distinct customer value .

And then we order the rows by the customer ascending, total descending, and id with

ORDER  BY customer, total DESC, id;

This query is done with Postgres.

Conclusion

To select the first row in each GROUP BY group with SQL, we can use DISTINCT ON.

Leave a Reply

Your email address will not be published. Required fields are marked *