Обсуждение: newbie sql question re: subqueries, order by, and limit

Hello all:

Witness my latest SQL trainwreck:

  SELECT uid, title FROM temptbl WHERE msgid NOT IN ( SELECT msgid FROM
  temptbl ORDER BY msgid DESC LIMIT 50 );

temptbl contains the data for a simple messageboard. The idea for this
query is to get all entries in tmptbl except the 50 newest ones (msgid
autoincrements). There's no special point to this excercise; it's just
something I thougt I'd try.

Problem is, in the psql shell, I get this reply:

  ERROR:  parser: parse error at or near "order"

Why is this happening? How can I fix it? Once again, I get the feeling
that I'm doing this the Wrong Way. What would be the Right Way to take
care of this task?

Thanks a lot,
--Tom

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