Skip site navigation (1) Skip section navigation (2)

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

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: Rahul_g(at)ip(dot)eth(dot)net, pgsql-bugs(at)postgresql(dot)org
Subject: Re: 'order by' and 'desc' not working in subquery using 'not in'
Date: 2001-07-31 16:06:13
Message-ID: Pine.BSF.4.21.0107310855230.1008-100000@megazone23.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-bugs
On Tue, 31 Jul 2001 pgsql-bugs(at)postgresql(dot)org wrote:

> Rahul Gade (Rahul_g(at)ip(dot)eth(dot)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)));



In response to

pgsql-bugs by date

Next:From: Tom LaneDate: 2001-07-31 16:25:11
Subject: Re: Using nulls with earthdistance operator crashes backend
Previous:From: Mark StosbergDate: 2001-07-31 14:58:56
Subject: Using nulls with earthdistance operator crashes backend

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group