I have a table that holds some banner paths. I also run a query on a page to randomly choose among those banners, and display the chosen one. However, I want to promote one of the banners, in a way that I would like to force it appear, let's say, every 10 page refreshes.

The query I use to randomly choose a banner is this:

SELECT location FROM random_logos ORDER BY RAND() LIMIT 1,0;

How could I alter it so that it forcible return the promoted banner every X number of page refreshes for EVERY visitor of the page?

I mean, even if I use another table to store the latest X (10 in my case) returned banners, I cannot be sure who refreshed the page for them. So, adding a check to that table will display the promoted banner on the 11th refresh to the user that happened to be the 11th visitor who refreshed the page. Then the process would start over.

I have thought of many ways, but each of them comes to a dead end for one reason or another.

I've tried multiple ways to select random rows from a database table. I've tried using NewId() and RAND(). Neither of them work. Is there some other way to select a random row?

works pefectly dude... you rock!

regarding the comment on unions and NewID()

another way besides using the view is to create a new collumn in your select, such as

SELECT bla1, bla2, NewID() as RandNum
FROM Table
WHERE whatever
UNION
SELECT bla1, bla2, NewID() as RandNum
FROM Table
WHERE whatever

ORDER BY RandNum

work perfect. greate work for me. Thanks.

To be "really random", you must have another null column where you can fill it if the corresponding row is selected. Note that the selection statement must include a where clause, on which it must select where the column is null.
eg. SELECT TOP 10 column1 FROM someTable WHERE column2 IS NULL ORDER BY NEWID()
Then you need to update the column2 setting it a value like 1.

Visit technoexperts.blogspot.com/.../...er-database.html for more details

I WANT TO KEY IN PERCENTAGE IN TEXT TO SELECT RANDOM LIKE EXAMPLE
IF I HAVE 100 EMPLOYEE SO I WANT KEY IN TEXT LIKE 10% SO THE SHOULD HOW

SELECT FILED
FROM TABLE
WHERE HERE SHOULD HOW TO PUT IF MY TEXT
ORDER BY RND()

i have more than one details corresponding to a particular rollnumber.
now i want to get these details in such a way that all details corresponding to a particular roll num should appear in one row all together.then what should i write in the query???

select top 10 * from sometable order by newid()

It gives 7,8,9 number of records sometime WHY??????????????????????????

Great code. I needed to randomly display 5 links on the home page. Your code worked like a charm. Thanks!

The thing that most surprised me about this is that it works in subsonic:

SubSonic.SqlQuery qry = Select.AllColumnsFrom<Gallery>().
Top("6").OrderAsc("NEWID()");

Amazing post..
Very Very Helpful
Thanks

Perfect. Thank you very much!

I am really glad that this was number one on Google for "sql server how to choose a number of random records"

Thanks

the function newid() works a lot better then the function rand()

I have been researching both the Rand() and NewId() methods and as stated above, NewId() is great for small sets of data but not so great with large datatables. However, knowing about your data should help! In my case, I counted the rows of data that would be return using my filters (where clauses) and then used that as a multiplier against Rand(). I knew what my desired sampling would be so here is what I did.

-- **************************************************

declare @threshold decimal(8,2)

set @threshold = 9.99

Declare @somedate char(8)
set @somedate = '20100518'

declare @rows int
Select @rows=COUNT(*) from sometable
where SOME_DATE = @somedate
and P_C_CODE = '3'
and C_A_DUE > @threshold
and R_T_CODE = '1'
and S_M_CODE != 'S'


Declare @rowno int

declare @i int
set @i = 1

-- This loop guarantees that we get unique rows in our sample.

while @i <= 60 -- the number of samples I want.
BEGIN
set @rowno = cast(RAND() * @rows as int)
if not exists(Select * from @t where RecNo = @rowno)
BEGIN
Insert into @t Values (@rowno)
set @i = @i + 1
END
END

Select
ROW_NUMBER() over (Order by R_T_Code) as RowNum
, MyField1 as fld1
, MyField2 as fld2
into #ctedata
from SomeTable
where SOME_DATE = @somedate
and P_C_CODE = '3'
and C_A_DUE > @threshold
and R_T_CODE = '1'
and S_M_CODE != 'S'

Select
*
from #ctedata a
inner join
@t b
on a.RowNum = b.RecNo
order by a.mbr_No

In my last post I left out the table @t

Declare @t table
(
RecNo int NOT NULL PRIMARY KEY
)

That should answer that question.

Thanks

it helped me........