>Nadya,
>
>>Mohammed Qasem gave me a quite interesting problem, which I am now (12:13am) trying to solve the most elegant way.
>
>Al and Sergey both posted what I consider the elegant way. But be warned prior to Europa TOP N can return more than N rows if there are ties in the last rows, so more work must be done to check for and eliminate them.

That's a good point. The first time I tested my routine I got 44 records returned, not 40. I was using RAND() instead of 1000000*RAND(). Since RAND() returns a value between 0 and 1, and decimals defaults to 2 I got a bunch of ties at 0.07, leading to the extra records.

Multiplying by a large value (I used a million, Sergey went even further) makes the chances of ties vanishingly small. But you're right, it's never zero. However, it's a simple matter to always take the first nn of the returned records if you know you're going to get at least nn random records.

I agree that this technique is elegant. However, Nadya asks for "efficient" in the thread title. As a guess, I'd say this technique isn't that efficient. For example, if you are trying to pull 40 random records out of a million, this technique means a million records will be pulled over the wire, sorted, then the top 40 sliced off the top. Doing 40 random GOTOs in the same million-record table may be more efficient.