Re: 'order by' and 'desc' not working in subquery using 'not in'

On Tue, 31 Jul 2001 pgsql-bugs@postgresql.org wrote:

> Rahul Gade (Rahul_g@ip.eth.net) reports a bug with a severity of 1

Umm, 1?  IIRC, order by in subqueries isn't even standard SQL, however
this should be taken care of in current sources so that you can use
order by ... limit in subqueries which sounds like what you're trying to
do (although your sample code doesn't include the limit)

As for temporary tables in plpgsql.  If you're using 7.1, you should be
able to make this work by using execute.

> The lower the number the more severe it is.
>
> Short Description
> 'order by' and 'desc' not working in subquery using 'not in'
>
> Long Description
> Hello,
>  I am facing two big problems,they may not be really big but in my case these are big :
>  1) temporary tables can not be created inside function using PL/pgsql
>  2) order by and desc keywords are not functioning inside subquery
>
> in the following query i am trying to delete all the rows except the top 20 rows from reporttable.
> How i can do this.
> i have done this in MSSQL using same syntax
>
> Out of these two 2nd problem is important for me,
> plase tell me what to do,
>                                     --- Thanks for response ----
>
>
> Sample Code
>  delete from reporttable where (srvServerid=serverid) and
> (rptreportid not in(select rptreportid from reporttable
> where (srvserverid=serverid order by rpttimestamp desc)));