How to randomly select rows in SQL??

Question

In my db, I have a table "customerNames" which has two columns "Id" and "Name" and approx. 1,000 results. I am creating a functionality where I have to pick up the 5 customers randomly every time. Can anyone tell me how to create a query which will get random 5 rows (Id, and Name) every time when query is executed.

edited

I am using MSSQL Server 2005.

Answer 1

simply retrieve 5 random integers ranging from the min value of Id to the max value of Id. then fetch the Id, name pair where Id is equal to the fetched random numbers

Answer 2

Random is not a common requirement for a Database, I was surprised to find a link for some SQL

Answer 3

Maybe this site will be of assistance.

For those who don't want to click through:

SELECT TOP 1 column FROM table
ORDER BY NEWID()

Answer 4

SELECT TOP 5 Id, Name FROM customerNames ORDER BY NEWID()

Answer 5

For SQL Server 2005, the if NOT EXISTS line eliminates duplicates:

declare @cnt_row_numbers numeric(10)
declare @cnt_test numeric(10)
declare @rn numeric(10)

declare @row_numbers TABLE (row_num numeric(10))

select @cnt_test = count(*) from test

select @cnt_row_numbers = 0

while (@cnt_row_numbers < 5)
begin
    select @rn = convert(integer, rand() * @cnt_test) + 1

    if NOT EXISTS (select 'X' from @row_numbers where row_num = @rn)
    begin
     insert into @row_numbers (row_num) values (@rn)
    end

    select @cnt_row_numbers = count(*) from @row_numbers
end

select * from @row_numbers

select * from (
select *, row_number() over (order by test_id) as row_num
from test
) results
where row_num IN (select row_num from @row_numbers)

If you try and use

select TOP 5 *, rand() random from test order by random

then rand() only gets called once, and it doesn't help, so to allow you to get 5 random values, the easiest way is to select 5 times.

Answer 6

In case someone wants a PostgreSQL solution:

select id, name
from customer
order by random()
limit 5;

Answer 7

http://www.petefreitag.com/item/466.cfm won't load, so I'm reposting it from Google's cache:

Select a random row with MySQL:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Select a random row with PostgreSQL:

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

Select a random row with Microsoft SQL Server:

SELECT TOP 1 column FROM table
ORDER BY NEWID()

Select a random row with IBM DB2

SELECT column, RAND() as IDX 
FROM table 
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

Select a random record with Oracle:

SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

Answer 8

If you want to add weighting, I would look at this post: http://www.bennadel.com/blog/1472-Ask-Ben-Selecting-A-Random-Row-From-A-Weighted-Filtered-Record-Set.htm

I don't want to post the whole article here, but this article is a really good read.