Greetings folks, This is a follow up to my initial message some time ago. Now I have got all the details together.
I have two tables. Table A - Which contains one row per entry Table B - Which contains multiple rows per entry Table B relates to Table A by a field say called ID. Table A in this case containers all the order header information while Table B contains the order lines. What I want to do is do a query joining table A with B and sorting firstly on a field in Table A then on several fields in Table B. The issue is in doing the table A sort is simple enough but as soon as you do as sort on table B fields all the data gets mixed up. So while below gives me the required data is does not work on the sorting front.. SELECT a.gdn_gdn, a.gdn_custref, a.gdn_date, a.gdn_address_name, a.gdn_method, b.gdn_stockref, b.gdn_row, b.gdn_bay, b.gdn_shelf FROM a LEFT JOIN b ON a.gdn_gdn = b.gdn_gdn ORDER BY a.gdn_method,b.gdn_row, b.gdn_bay, b.gdn_shelf Subsequent tries at using union querys, etc all failed with the same problem when it comes to the sorting. What I want to do is to do a sort group the sorted data together and then sort each group again to keep it all in order. Simple solution would be to create a dynamic select using a crosstab function, pivot table or something like this. However after reading up on this it appears this is impossible as you cannot have unknowns in a SELECT query. I then read this post "Using cursors for generating cross tables" from "Pavel Stehule's blog" which would seem to get the data in a form I could use. However while I can get the examples to work trying to apply it to my needs fails. Presumably because my data is more complicated. So has anyone any ideas how to achieve this. It seems so simple from a human sorting perspective I cannot believe it can not be done by a database, but after playing around with it for a very long time I am just getting nowhere. Regards Richard -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
