我想运行查询:
SELECT DISTINCT ON (address_id) purchases.address_id,purchases.* FROM "purchases" WHERE "purchases"."product_id" = 1 ORDER BY purchases.purchased_at DESC
我收到错误:
PG::Error: ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
添加排序在address_id修复问题,但我真的不想添加排序通过address_id。是否可以不通过address_id进行排序?
文档说:
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).
所以你必须添加address_id到order by。
这个查询可能会:
SELECT DISTINCT ON (address_id) purchases.address_id,purchases.* FROM "purchases" WHERE "purchases"."product_id" = 1 ORDER BY address_id,purchases.purchased_at DESC