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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Thomas Stepleton <tom(at)cs(dot)swarthmore(dot)edu>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: newbie sql question re: subqueries, order by, and limit
Date: 2000-12-30 04:25:28
Message-ID: 29172.978150328@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Thomas Stepleton <tom(at)cs(dot)swarthmore(dot)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

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Boris 2000-12-30 10:09:22 Searching datetime infos
Previous Message Charles Curley 2000-12-30 00:13:14 Re: date_part function