ORDER BY expression required in SELECT if DISTINCT

From: Pawel Veselov <pawel(dot)veselov(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: ORDER BY expression required in SELECT if DISTINCT
Date: 2013-03-17 21:57:13
Message-ID: CAMnJ+BezLoBBDYJSN+DntKnhseeS9Tfo-KGFQ6UJu_XcEDT6iQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Browse pgsql-general by date

  From Date Subject
Next Message Craig Ringer 2013-03-18 01:54:34 Re: [HACKERS] Trust intermediate CA for client certificates
Previous Message Gunnar "Nick" Bluth 2013-03-17 19:37:38 Re: High RAM usage on postgres