Re: Multiple column sorting with a decode

"Malcolm Dew-Jones" <yf110_at_vtn1.victoria.tc.ca> wrote in message news:43864701$1_at_news.victoria.tc.ca...
> Mark C. Stock (mcstockX_at_Xenquery.com) wrote:
>
> : <voidhack_at_gmail.com> wrote in message
> : news:1132866433.723828.221930_at_o13g2000cwo.googlegroups.com...
> : > Thank you for your answer.
> : > You are right, I did not express myself correctly.
> : >
> : > In my example I used column numbers to write the question more
> quickly,
> : > but even using column names, it still has the exact same result.
> : >
> : > Column 1 is not a constant, I simply used it as an example. What I am
> : > trying to do is provide the user excuting the report a choice on the
> : > multiple ordering. If :param =1 for example, then the sorting will be
> : > on 3 of the columns and if param = 2 on 4 (different) columns (for
> : > example).
> : >
> : > What might work is this: ORDER BY DECODE(...), DECODE(...),
> DECODE(...)
> : > providing I can fill the unused ones with something that does not
> : > change the result.
> : >
> : > Once again, thanks for your answer.
> : >
>
> : a SQL (not tool-specific) solution would be:
>
> : select ....
> : from ....
> : where ..
> : order by decode( :sort_type
> : , 1, cola||colb||colc
> : , 2, colb||colc
> : , 3, colc||cola
> : )
> : or use CASE instead of decode if your version supports it
>
> My concen with the above is if (for example)
>
> row 1 col1 = "ab" col2 = "x" => "abx"
> row 2 col1 = "a" col2 = "bz" => "abz"
>
> which should presumably sort as row2 row1, but if concatenated will sort
> as row1 row2.
>
> He(she?) could include enough decodes to accomodate all the columns by
> including a suitable default value for the "unused" decodes.
>
> order by
> decode(:sort_type,1,cola,2,colb,3,colc,0) ,
> decode(:sort_type,1,colb,2,colc,0) ,
> decode(:sort_type,1,colc,0);
>
> Or use the first (above) suggestion, but convert each column to a fixed
> width field to guarantee the correct ordering. The padding has to be
> carefully chosen.
>

you're right -- values would have to be converted to sortable strings and padded to common widths -- multiple decodes (or cases) would probably be a cleaner and easier to maintain, and perhaps more performant, solution

++ mcs Received on Thu Nov 24 2005 - 21:03:54 CST