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

Rahul Gade (Rahul_g(at)ip(dot)eth(dot)net) reports a bug with a severity of 1
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)));

No file was uploaded with this report

Hi Rahul,

Have you tried to quote each table name and column name by " , and constant by ' ? In your case, try

delete from "reporttable" where ( "srvServerid" = 'serverid') and
( "rptreportid" not in(select "rptreportid" from "reporttable"
where ( "srvserverid" = 'serverid' order by "rpttimestamp" desc)));

Appropriate quoting can help PostgreSQL understand SQL without ambiguity.

Eddy
Formatter/Programmer,
Internet Securities Inc. China (ISI)
Rm 202, Bright China Chang An Bldg.
7 Jian Guo Men Nei Ave.
Beijing 100005, China

E-mail: eddy(at)securities(dot)com
A Euromoney Institutional Investor Company

-----Original Message-----
From: pgsql-bugs-owner(at)postgresql(dot)org
[mailto:pgsql-bugs-owner(at)postgresql(dot)org]On Behalf Of
pgsql-bugs(at)postgresql(dot)org
Sent: Tuesday, July 31, 2001 4:16 PM
To: pgsql-bugs(at)postgresql(dot)org
Subject: [BUGS] 'order by' and 'desc' not working in subquery using 'not
in'

Rahul Gade (Rahul_g(at)ip(dot)eth(dot)net) reports a bug with a severity of 1
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)));

No file was uploaded with this report

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly

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)));