CODEWITHSUNDEEP
mysql
TRiG
TRiG

Reputation: 10643

Randomly select one of the last three entries from a MySQL table

I want to display one of the latest three news items. I came up with the following query:

SELECT
    *
FROM
    News
WHERE
    Id IN (
        SELECT
            Id
        FROM
            News
        WHERE
            Display=1
        ORDER BY
            Published DESC
        LIMIT 3
    )
ORDER BY
    RAND()
LIMIT 1

I expected that this would be horribly inefficient, but would work. In fact, it doesn't:

Error in query (1235): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

So my actual question is, Is there any way to randomly select one of the last three items from a table?

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+-------------------------+
| Variable_name           | Value                   |
+-------------------------+-------------------------+
| innodb_version          | 5.6.28                  |
| protocol_version        | 10                      |
| slave_type_conversions  |                         |
| version                 | 5.6.28-0ubuntu0.15.10.1 |
| version_comment         | (Ubuntu)                |
| version_compile_machine | x86_64                  |
| version_compile_os      | debian-linux-gnu        |
+-------------------------+-------------------------+
7 rows in set (0.00 sec)

Upvotes: 2

Views: 407

Answers (4)

sooper
sooper

Reputation: 6039

I've found a similar question, if you refer here

You must create a temporary table and then select one row at random from that. In your case it would be:

SELECT * FROM (
  SELECT * FROM `News` WHERE `Display` = `1`
ORDER BY `Published` DESC LIMIT 3
) AS temptable 
ORDER BY RAND()
LIMIT 1

As suggested before, it may be a good idea to select the last 10 and pick one randomly in server-side code.

Upvotes: 1

GG.
GG.

Reputation: 21854

The programming is also looking for workarounds when you're stuck with something too complex. :)

It's like when you want to say something in a foreign language and that you miss a word, so you say otherwise.

Here you can get the 3 last news, put in a table and select a random news.

If you code in PHP, look at array_rand().

Upvotes: -1

Mithun Sasidharan
Mithun Sasidharan

Reputation: 20940

Error means that MySQL does not support statements of the following form:

SELECT * FROM t1 WHERE s1 IN (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1)

Refer to the mysql documentation here

Upvotes: 0

Jan Hančič
Jan Hančič

Reputation: 53931

Try to avoid ORDER BY RAND(). A better approach here would be to fetch the latest three entries from your database. And then pick one randomly in your serverside code.

Upvotes: 2

Related Questions