I want to run this query:
SELECT DISTINCT ON (address_id) purchases.address_id, purchases.*
FROM purchases
WHERE purchases.product_id = 1
ORDER BY purchases.purchased_at DESC
But I get this error:
PG::Error: ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
Adding address_id as first ORDER BY expression silences the error, but I really don't want to add sorting over address_id. Is it possible to do without ordering by address_id?
Documentation says:
DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. [...] Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. [...] The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s).
So you'll have to add the address_id to the order by.
Alternatively, if you're looking for the full row that contains the most recent purchased product for each address_id and that result sorted by purchased_at then you're trying to solve a greatest N per group problem which can be solved by the following approaches:
The general solution that should work in most DBMSs:
SELECT t1.* FROM purchases t1
JOIN (
SELECT address_id, max(purchased_at) max_purchased_at
FROM purchases
WHERE product_id = 1
GROUP BY address_id
) t2
ON t1.address_id = t2.address_id AND t1.purchased_at = t2.max_purchased_at
ORDER BY t1.purchased_at DESC
A more PostgreSQL-oriented solution based on @hkf's answer:
SELECT * FROM (
SELECT DISTINCT ON (address_id) *
FROM purchases
WHERE product_id = 1
ORDER BY address_id, purchased_at DESC
) t
ORDER BY purchased_at DESC
Problem clarified, extended and solved here: Selecting rows ordered by some column and distinct on another
purchases.purchased_at DESC because for each address, it grabs the most recent purchase. –
Topper SELECT DISTINCT ON (purchases.purchased_at, address_id). However, two records with the same address_id but different purchased_at values will result in duplicates in the returned set. Make sure you are cognizant of the data you're querying. –
Overlord ORDER BY purchases.purchased_at DESC). –
Odaniel address_id for the same purchased_at –
Viper A subquery can solve it:
SELECT *
FROM (
SELECT DISTINCT ON (address_id) *
FROM purchases
WHERE product_id = 1
) p
ORDER BY purchased_at DESC;
Leading expressions in ORDER BY have to agree with columns in DISTINCT ON, so you can't order by different columns in the same SELECT.
Only use an additional ORDER BY in the subquery if you want to pick a particular row from each set:
SELECT *
FROM (
SELECT DISTINCT ON (address_id) *
FROM purchases
WHERE product_id = 1
ORDER BY address_id, purchased_at DESC -- get "latest" row per address_id
) p
ORDER BY purchased_at DESC;
If purchased_at can be NULL, use DESC NULLS LAST - and match your index for best performance. See:
Related, with more explanation:
DISTINCT ON without a matching ORDER BY. The first query requires an ORDER BY address_id inside the subquery. –
Cullen DISTINCT ON without ORDER BY in the same query. You get an arbitrary row from each set of peers defined by the DISTINCT ON clause in this case. Try it or follow the links above for details and links to the manual. ORDER BY in the same query (the same SELECT) just cannot disagree with DISTINCT ON. I did explain that, too. –
Odaniel ORDER BY is used” note in the docs because it does not make sense to me that the feature is implemented to be able deal with non-consecutive sets of values… yet won’t allow you to exploit that with an explicit ordering. Annoying. –
Cullen DISTINCT ON expressions (yet). –
Odaniel You can order by address_id in an subquery, then order by what you want in an outer query.
SELECT * FROM
(SELECT DISTINCT ON (address_id) purchases.address_id, purchases.*
FROM "purchases"
WHERE "purchases"."product_id" = 1 ORDER BY address_id DESC )
ORDER BY purchased_at DESC
select, I don't think this is production code? –
Overvalue address_id twice (without need). Many clients have problems with duplicate column names. ORDER BY address_id DESC is pointless and misleading. It does nothing useful in this query. The result is an arbitrary pick from each set of rows with the same address_id, not the row with the latest purchased_at. The ambiguous question did not ask for that explicitly, but that's almost certainly the OP's intention. In short: do not use this query. I posted alternatives with explanation. –
Odaniel purchased_at in a subquery at all, it will return just randomly ordered rows of distinct addres_ids –
Viper Window function may solve that in one pass:
SELECT DISTINCT ON (address_id)
LAST_VALUE(purchases.address_id) OVER wnd AS address_id
FROM "purchases"
WHERE "purchases"."product_id" = 1
WINDOW wnd AS (
PARTITION BY address_id ORDER BY purchases.purchased_at DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
address_id. The principle could work, though. Related examples: https://mcmap.net/q/28544/-custom-aggregate-function-in-postgresql or https://mcmap.net/q/28545/-retrieve-aggregates-for-arbitrary-time-intervals. But there are shorter and / or faster queries for the problem at hand. –
Odaniel For anyone using Flask-SQLAlchemy, this worked for me
from app import db
from app.models import Purchases
from sqlalchemy.orm import aliased
from sqlalchemy import desc
stmt = Purchases.query.distinct(Purchases.address_id).subquery('purchases')
alias = aliased(Purchases, stmt)
distinct = db.session.query(alias)
distinct.order_by(desc(alias.purchased_at))
query.distinct(foo).from_self().order(bar) –
Flowerlike Purchases.query? –
Jaunita from_self something undocumented? I cannot get it to work – Query and QuerySet raise AttributeError when I try to access it –
Mcleod It can also be solved using the following query along with other answers.
WITH purchase_data AS (
SELECT address_id, purchased_at, product_id,
row_number() OVER (PARTITION BY address_id ORDER BY purchased_at DESC) AS row_number
FROM purchases
WHERE product_id = 1)
SELECT address_id, purchased_at, product_id
FROM purchase_data where row_number = 1
You can also done this by using group by clause
SELECT purchases.address_id, purchases.* FROM "purchases"
WHERE "purchases"."product_id" = 1 GROUP BY address_id,
purchases.purchased_at ORDER purchases.purchased_at DESC
purchases has only the two columns address_id and purchased_at). Because of GROUP BY, you will need to use an aggregate function to get the value of each column not used for grouping, so they values will all be coming from different rows of the group unless you go through ugly and inefficient gymnastics. This can be fixed only by using window functions rather than GROUP BY. –
Cullen © 2022 - 2024 — McMap. All rights reserved.