[SQL] Sorting data based fields in two linked tables

[R. Smith]

Hi,

I am looking for a way to sort data returned from two tables with the
first sort based on a field from table A and the secord sort based on
the results of the first sort but the sort field is from table B.
While I can sort on either fields from either table, I cannot get it
to work on both. I have tried a crosstab query, but this a fails as
the number of rows returned from TABLE B for each row in TABLE A is an
unknown. I tried creating a temporary table, to then sort on, but this
failed as well. Example layout below:

Table A

ID FIELD1 FIELD2

Table B

ID, A.ID FIELD1,FIELD2

Output Based on sorting A.FIELD2, then B.FIELD1

A.ID,A.FIELD1,A.FIELD2,  B.FIELD1,B.FIELD2, B.FIELD1,B.FIELD2, B.FIELD1,B.FIELD2

Can anyone help me with this?

Regards

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Sorting of data from two tables

[R. Smith]

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

Re: [SQL] Sorting of data from two tables

[R. Smith]

On Sat, Sep 17, 2011 at 2:56 PM, David Johnston  wrote:
> On Sep 17, 2011, at 9:32, "R. Smith"  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

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql