CODEWITHSUNDEEP
mysql
Chris
Chris

Reputation: 764

Possible to select last 100 records in MySQL, then select random 10 from those results in one query?

I need to display 10 related videos on a video page that come from the same category as that video. The problem is that there could possibly be hundreds of thousands of rows for each category so running RAND() is out of the question and I would prefer not to create a myisam table that matches my innodb table and then full text search for related.

I am not sure if my idea is possible, but I would like to select 100 of the latest rows for that category ordered by date, and then select only 10 from that set randomly.

Is this possible and could you point me in the right direction please?

Upvotes: 0

Views: 579

Answers (2)

user650749
user650749

Reputation: 182

select * from (select * from table ORDER BY DESC LIMIT 100) ORDER BY rand() LIMIT 10

Upvotes: 0

Mara Ormston
Mara Ormston

Reputation: 1856

I'm assuming you have a simple table with an identity named ID, and you can do something like:

SELECT *
FROM (
  SELECT ID, Name, VideoFile
  FROM VideoTable
  ORDER BY ID DESC
  LIMIT 100
) Derived
ORDER BY RAND()
LIMIT 10

Upvotes: 5

Related Questions