Posted by Alexander Kuznetsov on 06/07/06 13:51

>
> select col1, col2, col3, col4, col5, col6
> from (SELECT TOP 1 col1, col2, col3, col4, col5, col6
> from table2
> where col2 = @variable2
> order by newID()) AS d
>

Erland,

My server has crashed and I cannot verify it right now, but I have a
feeling that newID() will be calculated for every row, which may be
inefficient. I was thinking of a more efficient way to get a random
row. Suppose there is an identity column, then you could
do something like this to get a random identity:

select min(id) from table2,
(select max(id) maxID, min(ID) minID from table2) t
where table2.id > t.minID + (t.maxID - t.minID)*rand()

What do you think?

[Back to original message]