Random selection from table 
Author Message
 Random selection from table
I would like to select a random set of 70 records from a
table. I have no idea how to do in SQL Server. In MySQL
it's like "SELECT * FROM table_name ORDER BY RAND() LIMIT
0,70)

Can anyone please help me out! Thanks a lot!

Greetz,
LX



Sat, 31 Jan 2004 17:25:02 GMT
 Random selection from table

One way is to leverage the fact that SS executes NEWID for each row in a result set.
You could do like this:

SELECT TOP 3 *
FROM authors
ORDER BY NEWID()

Note that this assumes that:
1. You're on W2K
2. SQL Server applies the ORDER BY before the TOP
3. SQL Server returns a different values from NEWID() for each row.

2 and 3 might break in future releases, i.e., the code is not "safe". I believe that
there is an FAQ article on random sorting. See my signature...
--
Tibor Karaszi, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com


Quote:
> I would like to select a random set of 70 records from a
> table. I have no idea how to do in SQL Server. In MySQL
> it's like "SELECT * FROM table_name ORDER BY RAND() LIMIT
> 0,70)

> Can anyone please help me out! Thanks a lot!

> Greetz,
> LX



Sat, 31 Jan 2004 18:14:29 GMT
 Random selection from table
I have completed this by using a query like

SELECT TOP 10 * FROM Authors
ORDER BY RAND(CONVERT(INT, CONVERT(VarBinary, NEWID())))

This way the records are always “random”.

Quote:
>-----Original Message-----
>I would like to select a random set of 70 records from a
>table. I have no idea how to do in SQL Server. In MySQL
>it's like "SELECT * FROM table_name ORDER BY RAND() LIMIT
>0,70)

>Can anyone please help me out! Thanks a lot!

>Greetz,
>LX
>.



Sat, 31 Jan 2004 21:59:57 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Random selection from table

2. Random selection from table

3. Random selection of rows in a query

4. SQL-->random selections

5. "Random" selection

6. Selection of Random Records

7. Random selection of numbers

8. Random Row Selection

9. random selection

10. Random row selection

11. random selections??

12. Random selection of resultset rows


 
Powered by phpBB® Forum Software