This is necessary because of how Postgres executes a query with DISTINCT ON internally. One additional detail with the query is you need to include the columns being distincted on in the ORDER BY. The query above uses this fact to get the pet with the oldest age for each owner. That means you can use SELECT DISTINCT ON with ORDER BY to get the row with the highest/lowest value of a given column for each combination of the values being distincted on.
One important detail of the behavior of SELECT DISTINCT ON is that it will always return the first row it encounters for each distinct pet owner. Will return the owner_id and name of one pet belonging to each distinct pet owner. SELECT DISTINCT ON (owner_id) owner_id, name FROM pets SELECT DISTINCT ON returns one row for every unique combination of values in the parens. The first part you need to understand is the behavior of SELECT DISTINCT ON. Let’s breakdown this query and see how it works. SELECT DISTINCT ON (owner_id) owner_id, name In this specific case, a query that solves the problem is: The general format of such a query looks like: The easiest way to solve this problem is with SELECT DISTINCT ON … ORDER BY 2.
If you try writing a query with a group by, you’ll try to write something like:īelieve it or not, there is no built-in aggregation that can be used to get the desired result.
This seems like a problem that could be solved by a group by, but it actually cannot be using the built-in aggregation functions 1. For example, you may have a pets table with columns name, age, and owner_id, and you may want to obtain the name of the oldest pet for each owner.
#POSTGRESQL SELECT DISTINCT HOW TO#
A common problem I’ve seen that many people are unfamiliar with how to solve is obtaining all of the rows that are the maximum of one value grouped by another value.
0 kommentar(er)
