We help IT Professionals succeed at work.

SELECT 10  records at random

Medium Priority
255 Views
Last Modified: 2012-05-05
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!!
Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
you where close:
: SELECT TOP 10 * FROM MY_TABLE WHERE DURATION>50 AND DURATION<7000 ORDER BY NEWID()

Open in new window

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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.
Faiga DiegelSr Database Engineer
CERTIFIED EXPERT

Commented:
replave the RAND with NEWID:

SELECT TOP 10 *
FROM MY_TABLE
WHERE DURATION>50 AND DURATION<7000
ORDER BY NEWID()

Explore More ContentExplore courses, solutions, and other research materials related to this topic.