Обсуждение: Sorting of data from two tables
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
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.
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
It would help if you gave an example comparing the above query results with your desired results. It would also help if you actually provided a detailed description of you goal instead of the generic one quoted above. Given your stated need the query does exactly what you requested.
David J.
On Sat, Sep 17, 2011 at 2:56 PM, David Johnston <polobo@yahoo.com> wrote: > On Sep 17, 2011, at 9:32, "R. Smith" <ship.quotes@gmail.com> wrote: > > > 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. > > > 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 > > > It would help if you gave an example comparing the above query results with > your desired results. It would also help if you actually provided a > detailed description of you goal instead of the generic one quoted above. > Given your stated need the query does exactly what you requested. > David J. Ok, here is some more specific information. The data stored is inventory pick data. We have a table which stores all the header information, addresses etc (Table A) and then each order line (Table B) which stores item to be picked and location. Now what we want is the data sorted by the method to be sent out which is stored in Table A and then the location of the item being picked which is stored in Table B. Now where there is more than one order line we just sort on the first order line. If we use the above query to sort the data, we end up with the order data all mixed up with one multiple orders spread amongst other orders, as the query does not know that some of the orders in Table A have multiple order lines in Table B. So we may pick order 1, then order 2, then order 3, and all of sudden come back to order 1, as there is a second order line to pick which is in a different location to the first order line in order 1, and order 2 and order 3. So simple solution is to drop it in to a cross tab query, then sort it as many times as you like as all the order data is in one row now rather then multiple ones. However the moment you run the crosstab query it fails, as the number columns are a variable which you cannot have. Hence my asking how you do this? Sample data output: Standard Query Order 1, Despatch Method A, Orderline1, Item Ref, Location A Order 2, Despatch Method A, Orderline1,Item Ref, Location A Order 3, Despatch Method A, Orderline1,Item Ref, Location A Order 1, Despatch Method A, Orderline2, Item Ref, Location B Order 4, Despatch Method A, Orderline1,Item Ref, Location B Order 5, Despatch Method B, Orderline1,Item Ref, Location A Order 6, Despatch Method B, Orderline1,Item Ref, Location B Order 7, Despatch Method B, Orderline1,Item Ref, Location B Order 5, Despatch Method B, Orderline2,Item Ref, Location B Crosstab Query Order 1, Despatch Method A, Orderline1, Item Ref, Location A, Orderline2, Item Ref, Location B, Order 2, Despatch Method A, Orderline1,Item Ref, Location A Order 3, Despatch Method A, Orderline1,Item Ref, Location A Order 4, Despatch Method A, Orderline1,Item Ref, Location B Order 5, Despatch Method B, Orderline1,Item Ref, Location A, Orderline2,Item Ref, Location B Order 6, Despatch Method B, Orderline1,Item Ref, Location B Order 7, Despatch Method B, Orderline1,Item Ref, Location B Richard
On Sep 25, 2011, at 12:37, "R. Smith" <ship.quotes@gmail.com> wrote: > On Sat, Sep 17, 2011 at 2:56 PM, David Johnston <polobo@yahoo.com> wrote: >> On Sep 17, 2011, at 9:32, "R. Smith" <ship.quotes@gmail.com> wrote: >> >> >> 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. >> >> >> 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 >> >> >> It would help if you gave an example comparing the above query results with >> your desired results. It would also help if you actually provided a >> detailed description of you goal instead of the generic one quoted above. >> Given your stated need the query does exactly what you requested. >> David J. > > Ok, here is some more specific information. The data stored is > inventory pick data. > We have a table which stores all the header information, addresses etc > (Table A) and > then each order line (Table B) which stores item to be picked and location. > > There isn't any useable way to output a variable number of columns. So, you need to decide how the data is going to be usedand specify the order by accordingly. You can use window functions to capture some of the order information from discontiguouslines; including ARRAY_AGG(). You should also ask yourself if you are trying to do too much with a single query/report. If you are stuck with CSV exportyour non-SQL options are limited but maybe your reporting environment can assist. If you are stuck with CSV you couldtry using array_agg and then convert the resultant array to a formatted string for output. CSV would surround the entireformatted output in quotes but maybe you could post-process the result to remove the quotes so that, say Excel, wouldsee the string as simply being additional columns in the file. David J.