Re: Sorting of data from two tables

On Sep 25, 2011, at 12:37, "R. Smith" <ship(dot)quotes(at)gmail(dot)com> wrote:
> On Sat, Sep 17, 2011 at 2:56 PM, David Johnston <polobo(at)yahoo(dot)com> wrote:
>> On Sep 17, 2011, at 9:32, "R. Smith" <ship(dot)quotes(at)gmail(dot)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 used and specify the order by accordingly. You can use window functions to capture some of the order information from discontiguous lines; 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 export your non-SQL options are limited but maybe your reporting environment can assist. If you are stuck with CSV you could try using array_agg and then convert the resultant array to a formatted string for output. CSV would surround the entire formatted output in quotes but maybe you could post-process the result to remove the quotes so that, say Excel, would see the string as simply being additional columns in the file.

David J.