MYSQL Select random from two rows

Question

Can someone help me with this query. I have this table for sample:

FILE Table

UID
file
uploaded_on_date
view_count

What I want to consider is the top 30 most recent uploaded file and top 30 most viewed file then randomly select from them and limit by 10.

I am new to this mysql complex queries. A sample query would be good and i will be able to understand it. Thanks.

ORDER BY RAND() type solutions will be very slow when the number of rows starts growing, just google for order by rand slow. Of course you won't notice this when selecting from 2*30 rows. — biziclop
– biziclop, Commented Feb 8, 2012 at 10:31

vulkanino · Accepted Answer · 2012-02-08 10:28:52Z

2

SELECT 
  *
FROM 
(
SELECT
  *
FROM
  tablefile
ORDER BY
  uploaded_on_date DESC
LIMIT 30
UNION SELECT
 *
FROM
  tablefile
ORDER BY
  view_count DESC
LIMIT 30
)
ORDER BY 
   RAND()
LIMIT 10;

answered Feb 8, 2012 at 10:28

vulkanino

9,1347 gold badges47 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

xiomai Over a year ago

Thanks for the reply vulkanino and xdazz, really close time answered very awesome.

xdazz · Accepted Answer · 2012-02-08 10:28:34Z

1

select * from (
    select * from table order by upload_on_date desc limit 30
    union
    select * from table order by view_count desc limit 30) t 
order by rand() limit 10

answered Feb 8, 2012 at 10:28

xdazz

161k38 gold badges255 silver badges278 bronze badges

2 Comments

vulkanino Over a year ago

oh my, exactly the same answer!!

xiomai Over a year ago

Thanks for the reply vulkanino and xdazz, really close time answered very awesome.

Collectives™ on Stack Overflow

MYSQL Select random from two rows

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related