During my db, I've got a table "customerNames" that has two posts "Id" and "Title" and approximately. 1,000 results. I'm developing a functionality where I must get the five clients at random each time. Can anybody let me know how to produce a query that will get random 5 rows (Id, and Title) each time when totally performed.

edited

I'm using MSSQL Server 2005.

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

Random isn't a common requirement of a Database, I had been surprised to locate a link for many SQL

Maybe this site will help.

For individuals who don't wish to click through:

SELECT TOP 1 column FROM table
ORDER BY NEWID()

Just in case someone wants a PostgreSQL solution:

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

http://www.petefreitag.com/item/466.cfm will not load, so I am reposting it from Google's cache:

Choose a random row with MySQL:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Choose a random row with PostgreSQL:

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

Choose a random row with Microsoft SQL Server:

SELECT TOP 1 column FROM table
ORDER BY NEWID()

Choose a random row with IBM DB2

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

Choose a random record with Oracle:

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

simply retrieve 5 random integers varying in the min worth of Id towards the max worth of Id. then fetch the Id, title pair where Id is equivalent to the fetched random amounts

For SQL Server 2005, the otherwise Is available line removes replicates:

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)

By trying and employ

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

then rand() only will get known as once, also it does not help, so to let you get 5 random values, the simplest strategy is to choose 5 occasions.

If you wish to add weighting, I'd see this publish: http://www.bennadel.com/blog/1472-Request-Ben-Choosing-A-Random-Row-From-A-Weighted-Strained-Record-Set.htm

I'd rather not publish the entire article here, but this information is an excellent read.