Re: newbie sql question re: subqueries, order by, and limit

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: newbie sql question re: subqueries, order by, and limit
Дата
Msg-id 29172.978150328@sss.pgh.pa.us
обсуждение исходный текст
Ответ на newbie sql question re: subqueries, order by, and limit  (Thomas Stepleton <tom@cs.swarthmore.edu>)
Список pgsql-novice
Thomas Stepleton <tom@cs.swarthmore.edu> writes:
> Witness my latest SQL trainwreck:

>   SELECT uid, title FROM temptbl WHERE msgid NOT IN ( SELECT msgid FROM
>   temptbl ORDER BY msgid DESC LIMIT 50 );
>   ERROR:  parser: parse error at or near "order"

Unfortunately, Pgsql 7.0 doesn't support ORDER BY (nor LIMIT) in
sub-SELECTs.  These features are implemented for 7.1, but in the
meantime what you have to do is run the sub-select into a temp
table, say

SELECT msgid INTO TEMP TABLE tmp1 FROM temptbl ORDER BY msgid DESC LIMIT 50;
SELECT uid, title FROM temptbl WHERE msgid NOT IN ( SELECT msgid FROM
tmp1 );
DROP TABLE tmp1;

            regards, tom lane

В списке pgsql-novice по дате отправления:

Предыдущее
От: Thomas Stepleton
Дата:
Сообщение: newbie sql question re: subqueries, order by, and limit
Следующее
От: "rob"
Дата:
Сообщение: Re: how to get trigger start function not procedure