Thread: 'order by' and 'desc' not working in subquery using 'not in'
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
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
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)));