In PostgreSQL this is typically simpler and faster (more performance optimization below):
SELECT **DISTINCT ON** (customer) id, customer, total FROM purchases ORDER BY customer, total DESC, id;
Or shorter (if not as clear) with ordinal numbers of output columns:
SELECT DISTINCT ON (2) id, customer, total FROM purchases ORDER BY 2, 3 DESC, 1;
total can be NULL (won't hurt either way, but you'll want to match existing indexes):
... ORDER BY customer, total DESC **NULLS LAST** , id;
DISTINCT ONis a PostgreSQL extension of the standard (where only
DISTINCTon the whole
SELECTlist is defined).
List any number of expressions in the
DISTINCT ONclause, the combined row value defines duplicates. The manual:
Obviously, two rows are considered distinct if they differ in at least one column value. Null values are considered equal in this comparison.
Bold emphasis mine.
DISTINCT ONcan be combined with
ORDER BY. Leading expressions in
ORDER BYmust be in the set of expressions in
DISTINCT ON, but you can rearrange order among those freely. Example. You can add additional expressions to
ORDER BYto pick a particular row from each group of peers. Or, as the manual puts it:
DISTINCT ONexpression(s) must match the leftmost
ORDER BYexpression(s). The
ORDER BYclause will normally contain additional expression(s) that determine the desired precedence of rows within each
id as last item to break ties:
"Pick the row with the smallest
id from each group sharing the highest
To order results in a way that disagrees with the sort order determining the first per group, you can nest above query in an outer query with another
ORDER BY. Example.
totalcan be NULL, you most probably want the row with the greatest non-null value. Add
NULLS LASTlike demonstrated. See:
SELECTlist is not constrained by expressions in
ORDER BYin any way. (Not needed in the simple case above):
You don't have to include any of the expressions in
You can include any other expression in the
SELECTlist. This is instrumental for replacing much more complex queries with subqueries and aggregate / window functions.
I tested with Postgres versions 8.3 – 12. But the feature has been there at least since version 7.1, so basically always.
The perfect index for the above query would be a multi-column index spanning all three columns in matching sequence and with matching sort order:
CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);
May be too specialized. But use it if read performance for the particular query is crucial. If you have
DESC NULLS LAST in the query, use the same in the index so that sort order matches and the index is applicable.
Effectiveness / Performance optimization
Weigh cost and benefit before creating tailored indexes for each query. The potential of above index largely depends on data distribution.
The index is used because it delivers pre-sorted data. In Postgres 9.2 or later the query can also benefit from an index only scan if the index is smaller than the underlying table. The index has to be scanned in its entirety, though.
For few rows per customer (high cardinality in column
customer), this is very efficient. Even more so if you need sorted output anyway. The benefit shrinks with a growing number of rows per customer.
Ideally, you have enough
work_memto process the involved sort step in RAM and not spill to disk. But generally setting
work_memtoo high can have adverse effects. Consider
SET LOCALfor exceptionally big queries. Find how much you need with
EXPLAIN ANALYZE. Mention of " Disk: " in the sort step indicates the need for more:
For many rows per customer (low cardinality in column
customer), a loose index scan (a.k.a. "skip scan") would be (much) more efficient, but that's not implemented up to Postgres 13. (An implementation for index-only scans is in development for Postgres 14. See here and here.)
For now, there are faster query techniques to substitute for this. In particular if you have a separate table holding unique customers, which is the typical use case. But also if you don't:
I had a simple benchmark here which is outdated by now. I replaced it with a detailed benchmark in this separate answer.