Thread: ORDER BY expression required in SELECT if DISTINCT

ORDER BY expression required in SELECT if DISTINCT

From
Pawel Veselov
Date:
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.