-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 I think you'll have to stick with doing your sorting (or merging) in your client. Don't think that PG recognizes the fact it's just a merge step.
Andreas Ambrus Wagner (IJ/ETH) wrote: > Dear All, > > I have several tables containing data sorted by 2 keys (neither are keys in > db terms (not unique), however). I would like to retrieve all rows from all > tables sorted by the same keys, essentially merging the contents of the > tables together. While I am completely aware of sort order not being a > (fundamental) property of an RDBMS table, I am also aware of indices and > clustering (in fact, data is inserted into the tables into the correct order, > and not consequently modified in any way). I have a union query like this one: > > select a,b,c,d,e from table1 union all > select a,b,c,d,e from table2 union all > etc... > select a,b,c,d,e from tablen order by a,b; > > Is there a way to prevent PostgreSQL from doing a full sort on the result set > after the unions have been completed? Even if I write > > (select a,b,c,d,e from table1 order by a,b) union all > (select a,b,c,d,e from table2 order by a,b) union all > etc... > (select a,b,c,d,e from tablen order by a,b) order by a,b; > > PostgreSQL does not seem to realise (maybe it should not be able to do this > trick anyway) that the last "order by" clause is merely a final merge step on > the ordered data sets. > > Is there a workaround for this within PostgreSQL (another type of query, > parameter tuning, stored procedure, anything) or should I use my back-up plan > of making separate queries and merging the results in the target language? > > Thanks a lot, > Ambrus > > -- > Wagner, Ambrus (IJ/ETH/GBD) > Tool Designer > GSDC Hungary > > Location: Science Park, A2 40 008 > Phone: +36 1 439 5282 > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.2 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGPy1CHJdudm4KnO0RAuKlAKCbYu2G/MYfmX9gAlSxkzA6KB4A+QCeIlAT USxhGD5XL7oGlIh+i2rVyN4= =APcb -----END PGP SIGNATURE----- ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
