Обсуждение: 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