Thread: ORDER BY expression required in SELECT if DISTINCT
Hi.
Anything I can do about this? Seems that the problem is that the query is a prepared statement, even if the parameters ($1 and $6) are the same.
ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list at character 493
STATEMENT: SELECT DISTINCT t0.package_name, ts_rank(t5.search_vector, to_tsquery($1)) FROM application t0 CROSS JOIN application t1 CROSS JOIN application_search t5 INNER JOIN application_state t2 ON t1.PUBLICAPPSTATE_ID = t2.id INNER JOIN application_state_price t3 ON t2.id = t3.E_APPSTATE_ID INNER JOIN price t4 ON t3.PRICES_ID = t4.id WHERE (t4.currency = $2 AND t2.frontState = $3 AND t0.package_name = t5.APP_PACKAGE_NAME AND ts_match_vq(t5.search_vector, to_tsquery($4)) = $5 AND 1 = 1) ORDER BY ts_rank(t5.search_vector, to_tsquery($6)) ASC LIMIT $7
This works:
SELECT DISTINCT t0.package_name, ts_rank(t5.search_vector, to_tsquery('aaa'))
FROM application t0
CROSS JOIN application t1
CROSS JOIN application_search t5
INNER JOIN application_state t2
ON t1.PUBLICAPPSTATE_ID = t2.id
INNER JOIN application_state_price t3
ON t2.id = t3.E_APPSTATE_ID
INNER JOIN price t4
ON t3.PRICES_ID = t4.id
WHERE
(t4.currency = 'USD' AND
t2.frontState = 'PUBLISHED' AND
t0.package_name = t5.APP_PACKAGE_NAME AND
ts_match_vq(t5.search_vector, to_tsquery('aaa')) AND 1 = 1)
ORDER BY ts_rank(t5.search_vector, to_tsquery('aaa')) ASC
Thank you,
Pawel.