rand() vs order by

#1

Hi!
By results of small database competition in one blog was
born my question.
SA10/SA12
QUERY: select rand() as a from some_table order by a;
generate dataset filled by random values, but this resultset
not sorted.
Tha same query in oracle, postgresql and mysql produce
sorted resultset - they win competition.
MSSQL even can't generate resultset with one constant random
value

Is it possible to get correct sorted by random value
resultset from sa?

THANKS!

#2

sorry, small mistake: MSSQL generate resultset with only one
constant random value for all dataset

#3

i have not test but i think you may try to use the derived table method to sort this result set;

select rand_col
from (
select rand_col = rand()
from some_table
) t1
order by rand_sol

hth

"Alexey" ¦b¶l¥ó±i¶K¤º®e¥D¦® 4c81e5dd.6d8f.1681692777 (AT) sybase (DOT) com ¤¤¼¶¼g...

Quote:

Hi!
By results of small database competition in one blog was
born my question.
SA10/SA12
QUERY: select rand() as a from some_table order by a;
generate dataset filled by random values, but this resultset
not sorted.
Tha same query in oracle, postgresql and mysql produce
sorted resultset - they win competition.
MSSQL even can't generate resultset with one constant random
value

Is it possible to get correct sorted by random value
resultset from sa?

THANKS!

#4

I have test deruved table method and result bring me a
sorrow:
rand_col
0.9977807826352216
0.9977807826352216
....
0.9977807826352216
0.9977807826352216
0.9977807826352216

VERY BAD

I am unexpected this result.

Quote:

i have not test but i think you may try to use the derived
table method to sort this result set;

select rand_col
from (
select rand_col = rand()
from some_table
) t1
order by rand_sol

#5

One more strange result

Quote:

select rand_col*1.0
from (
select rand_col = rand()
from some_table
) t1
order by rand_sol

produce:
rand() as rand_col*1.0
0.59280651928522
0.2991696266919233
0.1439158111549522
0.7930380812813705
0.5910320959943496
0.47643737703395883

#6

if this method do not work, i need you need to select the result to temp table first, then use second select to with order by to
sort the records;

select rand_col into #t1 from some_table
select rand_col from #t1 order by rand_col

"Alexey" ¦b¶l¥ó±i¶K¤º®e¥D¦® 4c8365b6.460.1681692777 (AT) sybase (DOT) com ¤¤¼¶¼g...

Quote:

One more strange result
select rand_col*1.0
from (
select rand_col = rand()
from some_table
) t1
order by rand_sol

produce:
rand() as rand_col*1.0
0.59280651928522
0.2991696266919233
0.1439158111549522
0.7930380812813705
0.5910320959943496
0.47643737703395883

#7

I have confirmed this with SA 12 GA - and the wrongly repeated rows show
up, too, when using the "select rand() as a from some_table" as a common
table expression and then do an order by over the CTE.

Seems to be a case of "over-optimization" a non-deterministic function
and as such as a bug, methinks.

Regards
Volker

Alexey wrote:

Quote:

I have test deruved table method and result bring me a
sorrow:
rand_col
0.9977807826352216
0.9977807826352216
...
0.9977807826352216
0.9977807826352216
0.9977807826352216

VERY BAD

I am unexpected this result.

i have not test but i think you may try to use the derived
table method to sort this result set;

select rand_col
from (
select rand_col = rand()
from some_table
) t1
order by rand_sol

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

rand() vs order by

sybase.public.sqlanywhere.general sybase.public.sqlanywhere.general

rand() vs order by - 09-04-2010 , 01:23 AM

Re: rand() vs order by - 09-04-2010 , 01:25 AM

Re: rand() vs order by - 09-04-2010 , 11:04 AM

Re: rand() vs order by - 09-05-2010 , 03:53 AM

Re: rand() vs order by - 09-05-2010 , 04:41 AM

Re: rand() vs order by - 09-05-2010 , 10:08 AM

Re: rand() vs order by - 09-07-2010 , 02:52 AM