CODEWITHSUNDEEP
mysqlsql
Kritika Shrivastava
Kritika Shrivastava

Reputation: 129

Select top 10 rows from database and display result randomly

I want to display trending Ads on my site on the basis of number of clicks.

My SQL Query looks like this:

  SELECT  ad_id, clicks from ads ORDER BY  clicks DESC LIMIT 5

Result:
ad_id   clicks
3393    2204
4495    1208
2399    932
2780    777
3316    679

I want to display this result randomly every time page refresh.

I tried using 

SELECT  * from ads ORDER BY  clicks DESC, RAND() LIMIT 10

But this is not working giving the same result every time on page refresh.

If I use like:

SELECT  ad_id, clicks from ads ORDER BY RAND(), clicks DESC LIMIT 10
ad_id   clicks
9762    0
6305    1
4040    17
11598   0
11347   0

It is showing data randomly but now the highest number of clicks is zero.

Can you suggest me how to display top clicks result randomly on every page refresh.

Upvotes: 0

Views: 342

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39497

If you want to get top 10 rows and then display them in random order, use this:

select *
from (
    select *
    from ads
    order by clicks desc LIMIT 10
    ) t
order by rand() desc

If you want to get 10 randomly chosen records sorted in descending order of clicks, try this:

select *
from (
    select *
    from ads
    order by RAND() LIMIT 10
    ) t
order by clicks desc

It finds 10 random records in subquery and then sorts it afterwards.

Or perhaps you want to get 10 random records out of some top , say 100, rows sorted in descending order of clicks:

select *
from (
    select *
    from (
        select *
        from ads
        order by click desc LIMIT 100  -- change this as per your needs
        ) t
    order by rand() limit 10
    ) t
order by clicks desc

Upvotes: 1

Related Questions