?
Solved

SELECT 10  records at random

Posted on 2008-01-25
4
Medium Priority
?
233 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!!
0
Comment
Question by:ggmisadmin
  • 2
4 Comments
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20743528
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 20743568
you where close:
: SELECT TOP 10 * FROM MY_TABLE WHERE DURATION>50 AND DURATION<7000 ORDER BY NEWID()

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20743577
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.
0
 
LVL 15

Expert Comment

by:Faiga Diegel
ID: 20743646
replave the RAND with NEWID:

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

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question