Re: Random Order BY
Date: 1998/08/18
Message-ID: <35D9990E.BC142C8D_at_execpc.com>#1/1
A DECODE in the order by clause may be what you need. I have done some rather unique orderings based on an input parameter. The performance hit is not very noticeable since the result set is the same size regardless of the order by. The sort does take a little time to calculate the the decode results.
This is a piece of code that joins three tables (l--600,000+ rows, m--500,000+ rows, st_ovr(st_def) -- 40 rows) and completes in less than 1 second with a final result set of 5,000 to 30,000 rows on a 200Mhz Pentium box running NT.
ORDER BY
DECODE(sort_option, 5, l.actual_start_dt, null), DECODE(sort_option, 6, l.title, null), DECODE(sort_option, 0, 1, 1,NVL(st_ovr.display_seq, st_def.display_seq), 2,NVL(st_ovr.display_seq, st_def.display_seq), 1), UPPER(DECODE(sort_option, 0,to_char(grid_start_dt,'HH24:MI'), 1,lpad(m.channel_number,10,'0'), 3,lpad(m.channel_number,10,'0'), NVL(m.name_ovr, l.station_name))), UPPER(DECODE(sort_option, 0,to_char(grid_start_dt,'HH24:MI'), 2,lpad(m.channel_number,10,'0'), 4,lpad(m.channel_number,10,'0'), NVL(m.name_ovr, l.station_name))), l.serv_id, l.grid_start_dt
This covers 8 different ordering rules--sort_option between 0 and 6 and 'other'. The main thing to watch for is within a decode function all possible returns need to be of the same data type (cannot order by mixed types). Note: channel_number is missed named here since it has been changed to data type varchar2.
Keith McKendry
Whittman-Hart, Inc.
TurkBear wrote:
> "Josef Huber" <josef.huber_at_ennstal.at> wrote:
>
> >Hello,
> >
> >I have often sql statements with the same data and want sort the
> >data in a different order.
> >Is there a way to do so a random sort ?
> >
> >
> >Ciao,
> >Joe
> >
> >
>
> The order of the results is determined by the 'ORDER BY' clause, so
> using a
> different one will alter the order ( this may seem obvious, but you
> asked)
>
> To get a 'random' order, you could use the 'Select .....from.....'
> clause with
> NO order by specification and the results will be in unordered form (
> Oracle
> stores and retrieves in a random-like manner, if no instructions are
> given )
>
> To reply please remove the 'nospam' part of the address
Received on Tue Aug 18 1998 - 00:00:00 CEST