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

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

From
pgsql-bugs@postgresql.org
Date:
Rahul Gade (Rahul_g@ip.eth.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

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

From
"Dong, Meng"
Date:
SGkgUmFodWwsDQoNCkhhdmUgeW91IHRyaWVkIHRvIHF1b3RlIGVhY2ggdGFi
bGUgbmFtZSBhbmQgY29sdW1uIG5hbWUgYnkgICIgLCBhbmQgY29uc3RhbnQg
YnkgJyA/IEluIHlvdXIgY2FzZSwgdHJ5DQoNCg0KIGRlbGV0ZSBmcm9tICJy
ZXBvcnR0YWJsZSIgd2hlcmUgKCAic3J2U2VydmVyaWQiID0gJ3NlcnZlcmlk
JykgYW5kDQooICJycHRyZXBvcnRpZCIgbm90IGluKHNlbGVjdCAicnB0cmVw
b3J0aWQiIGZyb20gInJlcG9ydHRhYmxlIg0Kd2hlcmUgKCAic3J2c2VydmVy
aWQiID0gJ3NlcnZlcmlkJyBvcmRlciBieSAicnB0dGltZXN0YW1wIiBkZXNj
KSkpOw0KDQoNCkFwcHJvcHJpYXRlIHF1b3RpbmcgY2FuIGhlbHAgUG9zdGdy
ZVNRTCB1bmRlcnN0YW5kIFNRTCB3aXRob3V0IGFtYmlndWl0eS4NCg0KDQoN
Cg0KDQogICAgDQoNCg0KRWRkeQ0KRm9ybWF0dGVyL1Byb2dyYW1tZXIsDQpJ
bnRlcm5ldCBTZWN1cml0aWVzIEluYy4gQ2hpbmEgKElTSSkNClJtIDIwMiwg
QnJpZ2h0IENoaW5hIENoYW5nIEFuIEJsZGcuDQo3IEppYW4gR3VvIE1lbiBO
ZWkgQXZlLg0KQmVpamluZyAxMDAwMDUsIENoaW5hDQoNCkUtbWFpbDogZWRk
eUBzZWN1cml0aWVzLmNvbQ0KQSBFdXJvbW9uZXkgSW5zdGl0dXRpb25hbCBJ
bnZlc3RvciBDb21wYW55DQoNCi0tLS0tT3JpZ2luYWwgTWVzc2FnZS0tLS0t
DQpGcm9tOiBwZ3NxbC1idWdzLW93bmVyQHBvc3RncmVzcWwub3JnDQpbbWFp
bHRvOnBnc3FsLWJ1Z3Mtb3duZXJAcG9zdGdyZXNxbC5vcmddT24gQmVoYWxm
IE9mDQpwZ3NxbC1idWdzQHBvc3RncmVzcWwub3JnDQpTZW50OiBUdWVzZGF5
LCBKdWx5IDMxLCAyMDAxIDQ6MTYgUE0NClRvOiBwZ3NxbC1idWdzQHBvc3Rn
cmVzcWwub3JnDQpTdWJqZWN0OiBbQlVHU10gJ29yZGVyIGJ5JyBhbmQgJ2Rl
c2MnIG5vdCB3b3JraW5nIGluIHN1YnF1ZXJ5IHVzaW5nICdub3QNCmluJw0K
DQoNClJhaHVsIEdhZGUgKFJhaHVsX2dAaXAuZXRoLm5ldCkgcmVwb3J0cyBh
IGJ1ZyB3aXRoIGEgc2V2ZXJpdHkgb2YgMQ0KVGhlIGxvd2VyIHRoZSBudW1i
ZXIgdGhlIG1vcmUgc2V2ZXJlIGl0IGlzLg0KDQpTaG9ydCBEZXNjcmlwdGlv
bg0KJ29yZGVyIGJ5JyBhbmQgJ2Rlc2MnIG5vdCB3b3JraW5nIGluIHN1YnF1
ZXJ5IHVzaW5nICdub3QgaW4nDQoNCkxvbmcgRGVzY3JpcHRpb24NCkhlbGxv
LA0KIEkgYW0gZmFjaW5nIHR3byBiaWcgcHJvYmxlbXMsdGhleSBtYXkgbm90
IGJlIHJlYWxseSBiaWcgYnV0IGluIG15IGNhc2UgdGhlc2UgYXJlIGJpZyA6
DQogMSkgdGVtcG9yYXJ5IHRhYmxlcyBjYW4gbm90IGJlIGNyZWF0ZWQgaW5z
aWRlIGZ1bmN0aW9uIHVzaW5nIFBML3Bnc3FsDQogMikgb3JkZXIgYnkgYW5k
IGRlc2Mga2V5d29yZHMgYXJlIG5vdCBmdW5jdGlvbmluZyBpbnNpZGUgc3Vi
cXVlcnkNCiAgIA0KaW4gdGhlIGZvbGxvd2luZyBxdWVyeSBpIGFtIHRyeWlu
ZyB0byBkZWxldGUgYWxsIHRoZSByb3dzIGV4Y2VwdCB0aGUgdG9wIDIwIHJv
d3MgZnJvbSByZXBvcnR0YWJsZS4NCkhvdyBpIGNhbiBkbyB0aGlzLg0KaSBo
YXZlIGRvbmUgdGhpcyBpbiBNU1NRTCB1c2luZyBzYW1lIHN5bnRheA0KDQpP
dXQgb2YgdGhlc2UgdHdvIDJuZCBwcm9ibGVtIGlzIGltcG9ydGFudCBmb3Ig
bWUsDQpwbGFzZSB0ZWxsIG1lIHdoYXQgdG8gZG8sDQogICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAtLS0gVGhhbmtzIGZvciByZXNwb25z
ZSAtLS0tDQoNCg0KU2FtcGxlIENvZGUNCiBkZWxldGUgZnJvbSByZXBvcnR0
YWJsZSB3aGVyZSAoc3J2U2VydmVyaWQ9c2VydmVyaWQpIGFuZA0KKHJwdHJl
cG9ydGlkIG5vdCBpbihzZWxlY3QgcnB0cmVwb3J0aWQgZnJvbSByZXBvcnR0
YWJsZQ0Kd2hlcmUgKHNydnNlcnZlcmlkPXNlcnZlcmlkIG9yZGVyIGJ5IHJw
dHRpbWVzdGFtcCBkZXNjKSkpOw0KDQpObyBmaWxlIHdhcyB1cGxvYWRlZCB3
aXRoIHRoaXMgcmVwb3J0DQoNCg0KLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tKGVuZCBvZiBicm9hZGNhc3QpLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tDQpUSVAgMzogaWYgcG9zdGluZy9yZWFkaW5nIHRocm91Z2ggVXNlbmV0
LCBwbGVhc2Ugc2VuZCBhbiBhcHByb3ByaWF0ZQ0Kc3Vic2NyaWJlLW5vbWFp
bCBjb21tYW5kIHRvIG1ham9yZG9tb0Bwb3N0Z3Jlc3FsLm9yZyBzbyB0aGF0
IHlvdXINCm1lc3NhZ2UgY2FuIGdldCB0aHJvdWdoIHRvIHRoZSBtYWlsaW5n
IGxpc3QgY2xlYW5seSANCg0K

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

From
Stephan Szabo
Date:
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)));