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

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

From: "Dong, Meng" <eddy(at)securities(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 09:30:27
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
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.


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

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

In response to

pgsql-bugs by date

Next:From: Mark StosbergDate: 2001-07-31 14:58:56
Subject: Using nulls with earthdistance operator crashes backend
Previous:From: pgsql-bugsDate: 2001-07-31 08:15:49
Subject: 'order by' and 'desc' not working in subquery using 'not in'

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