ggmisadmin
asked on
SELECT 10 records at random
Hello everybody,
I am trying to figure out how to write a SQL query which outputs 10 records at random from a table given a certan condition.
I am using this query: SELECT TOP 10 * FROM MY_TABLE WHERE DURATION>50 AND DURATION<7000 ORDER BY RAND()
My records include also dates and I can see that such 10 outputs are still in sequence (from the dates) which is not what I want.
Any idea haw I can get it? I am using SQL 2000.
Thank you for your help!!
I am trying to figure out how to write a SQL query which outputs 10 records at random from a table given a certan condition.
I am using this query: SELECT TOP 10 * FROM MY_TABLE WHERE DURATION>50 AND DURATION<7000 ORDER BY RAND()
My records include also dates and I can see that such 10 outputs are still in sequence (from the dates) which is not what I want.
Any idea haw I can get it? I am using SQL 2000.
Thank you for your help!!
SQL_SERVER_DBA
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1057929,00.html?bucket=ETA
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
explanation: ORDER BY RAND() will not work as "expected", as RAND() will generate 1 single numerical value, and indicate the column number by which you would be sorting.
replave the RAND with NEWID:
SELECT TOP 10 *
FROM MY_TABLE
WHERE DURATION>50 AND DURATION<7000
ORDER BY NEWID()
SELECT TOP 10 *
FROM MY_TABLE
WHERE DURATION>50 AND DURATION<7000
ORDER BY NEWID()