Re: Multiple column sorting with a decode

From: Malcolm Dew-Jones <yf110_at_vtn1.victoria.tc.ca>
Date: 24 Nov 2005 15:04:33 -0700
Message-ID: <43864701$1@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.

--

This programmer available for rent.

Received on Thu Nov 24 2005 - 16:04:33 CST