0

I am trying to display exactly 6 random 'entertainment' entries, but with my current query it's getting a random number between 1 and 6, and displaying that number of entries. How do I update this query in order to make it display exactly 6 random entertainment entries from my Articles table? Here's my current query:

 SELECT
    r1.*
FROM
    Articles AS r1
    INNER JOIN (SELECT(RAND() * (SELECT MAX(id) FROM Articles)) AS id) AS r2
WHERE
    r1.id >= r2.id
    AND r1.category = 'entertainment'
LIMIT 6;

The table structure is as follows:

table Articles
 - id (int)
 - category (varchar)
 - title (varchar)
 - image (varchar)
 - link (varchar)
 - Counter (int)
 - dateStamp (datetime)
Pevara
  • 14,242
  • 1
  • 34
  • 47
user2896120
  • 3,180
  • 4
  • 40
  • 100
  • can you provide us some insight to your table structure. What are the relevant columns and what does the `r1` table stand for? – Pevara Apr 01 '16 at 21:50
  • @Pevara I have 7 columns in the Articles table. id (unique), category (the category of the article, i.e entertainment), title (the title of the article), image (the image URL of the article), link (the URL of the article), Counter (the number of views the article has), dateStamp (the date the article was published). – user2896120 Apr 01 '16 at 22:11

1 Answers1

1

Try:

SELECT
    r1.*
FROM
    Articles AS r1
WHERE
    r1.category = 'entertainment'
ORDER BY RAND()
LIMIT 6;

I guess that is what you tried with your subquery?

Tom
  • 133
  • 6
  • I am trying to not use ORDER BY RAND() due to it being inefficient with performance – user2896120 Apr 01 '16 at 22:10
  • Have you tried http://stackoverflow.com/questions/6541644/selecting-random-rows-with-mysql/6542113#6542113? – Tom Apr 01 '16 at 22:12
  • `ORDER BY RAND()` doesn't scale well indeed, but unless you are expecting to have thousands of articles I suspect the impact will be negligeable. And I'm a big fan of keeping things simple whenever possible... – Pevara Apr 01 '16 at 22:21