rand() vs order by

[unknown]

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!

[unknown]

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

[unknown]

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

[HarryLai]

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

Post by unknown
One more strange result

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

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

[unknown]

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

[Volker Barth]

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

Post by unknown
I have test deruved table method and result bring me a
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

[HarryLai]

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

Post by unknown
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!