How to Select the First Row in a PostgreSQL Group By Query?

Select first row in PostgreSQL grouped query

When working with grouped data, you often need to retrieve specific rows, such as the first or last row in each group. This tutorial aims to answer how to select the first row in a grouped query in PostgreSQL.

Solution: DISTINCT ON

PostgreSQL provides a convenient function called DISTINCT ON, which allows selecting the first (or last) occurrence of duplicate rows within a group. The syntax is as follows:

<code class="language-sql">SELECT DISTINCT ON (DISTINCT_COLUMNS) COLUMNS
FROM TABLE
ORDER BY GROUPING_COLUMN, SORT_ORDER;</code>

Applies to the given problem

In the example provided, we want to retrieve the first row for each customer in the purchases table based on the highest total value. We can use DISTINCT ON to achieve the following:

<code class="language-sql">SELECT DISTINCT ON (customer)
       id, customer, total
FROM   purchases
ORDER  BY customer, total DESC, id;</code>

This query will output the desired results:

FIRST(id)	customer	FIRST(total)
1	Joe	5
2	Sally	3

Explanation

• DISTINCT ON (customer) Specifies that we want to group the results by the customer column and select different values.
• ORDER BY customer, total DESC, id Sort the results first by customer (for grouping), then by total in descending order (to find the first row with the highest total value), and finally by id to resolve conflicts (if any).

Additional Notes

The
• DISTINCT ON clause must be contained within the ORDER BY clause, but the standard does not require an additional id column.
• If the total column can contain null values, then NULLS LAST should be added to the ORDER BY clause:

<code class="language-sql">ORDER BY customer, total DESC NULLS LAST, id;</code>

Conclusion

The DISTINCT ON functionality in PostgreSQL provides an efficient and flexible way to select the first (or last) row in a grouped query. By leveraging this feature, developers can easily analyze grouped data and retrieve necessary information.

The above is the detailed content of How to Select the First Row in a PostgreSQL Group By Query?. For more information, please follow other related articles on the PHP Chinese website!