From: | Michael Wood <esiotrot(at)gmail(dot)com> |
---|---|
To: | Mary Anderson <maryfran(at)demog(dot)berkeley(dot)edu> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: How many columns can I have in an ORDER BY clause? |
Date: | 2010-02-26 09:01:05 |
Message-ID: | 5a8aa6681002260101w44206f70g77db05f72ec25c5b@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On 26 February 2010 03:11, Mary Anderson <maryfran(at)demog(dot)berkeley(dot)edu> wrote:
> Hi,
>
> I have a query that has an ORDER BY clause that appears to break.
> It has 12 or 13 columns. Have I exceeded postgresql's sorting capacity?
>
> Here is my query:
> BIG_SELECT_cmd = SELECT
>
> value,x.d15_dv_id,x.d5_dv_id,x.dm1_dv_id,x.dm2_dv_id,x.d8_dv_id,x.d12_dv_id,v.data_batch_id
>
> FROM display.perm_tmp_xprod_table_550 x LEFT JOIN
> display.perm_tmp_data_values_550 v
> ON x.d15_dv_id = v.d15_dv_id
> AND x.d5_dv_id = v.d5_dv_id
> AND x.dm1_dv_id = v.dm1_dv_id
> AND x.dm2_dv_id v.dm2_dv_id
> AND x.d8_dv_id = v.d8_dv_id
> AND x.d12_dv_id =v.d12_dv_id
>
> ORDER BY
> x.d15_group_order,x.d15_value_rank,
This will order by x.d15_group_order. If any of the values in that
column are the same, then those rows will be sorted by
x.d15_value_rank.
> x.d5_group_order,x.d5_value_rank,
If any rows have the same x.d15_group_order and x.d15_group_rank then
those matching rows will be sorted by x.d5_group_order. If there are
any that have the same d15_* and d5_group_order then those will be
sorted by x.d5_group_rank.
> x.dm1_group_order,x.dm1_value_rank,
> x.dm2_group_order,x.dm2_value_rank,
> x.d8_group_order,x.d8_value_rank,
> x.d12_group_order,x.d12_value_rank,
> v.DMEM_status,
etc.
So by the time you get to x.dm1_group* and x.dm2_group* you probably
don't have any rows with all the other columns the same and therefore
the order of the values in dm1* and dm2* will not appear to be sorted
(when you look at the whole column).
> '2050-01-01'::DATE - v.data_batch_created_date
This is a strange ORDER BY clause. I think you want
"v.data_batch_created_date DESC".
> I checked the underlying tables. They seem to be reasonable -- no nulls
> But the dm1 values and the dm2 values do not sort at all.
e.g. if you have the following values:
a,b,c,d
=======
0,0,9,5
0,0,1,3
0,0,7,9
0,0,2,8
0,1,2,6
1,3,5,7
then SELECT * FROM table_name ORDER BY a, b, c, d; would give you:
a,b,c,d
=======
0,0,1,3
0,0,2,8
0,0,7,9
0,0,9,5
0,1,2,6
1,3,5,7
If you look at columns c and d without considering a and b, they look
unsorted, only because the ordering depends on all columns together
with a being the most important, b next, etc.
If this does not explain what you're seeing, please provide some
example output and what you were expecting to see instead.
--
Michael Wood <esiotrot(at)gmail(dot)com>
From | Date | Subject | |
---|---|---|---|
Next Message | Louis Becker | 2010-02-26 09:06:28 | Re: Importing lines of variable length from ASCII |
Previous Message | Jasen Betts | 2010-02-26 08:52:14 | Re: Leap Years |