Boris Boris - 4 months ago 15
SQL Question

Select only 5 random rows in the last 50 entries

I'm just starting with MySQL so I would like to know how can I select only 5 random rows in the last 50 entries of my database? I hope you understand my question.

I'm using PDO and what I have now is this:

$otherChoiseRig = $bdd->query("SELECT * FROM articulos WHERE cat = '$ArtCat' ORDER BY RAND() ");
$otherChoiseRig2 = $otherChoiseRig->fetchAll(PDO::FETCH_ASSOC);


Then I use a PHP foreach boucle...

Thank you

Answer

The challenge is determining the last 50 entries. Assuming you have an auto-incremented id, you can do:

SELECT a.*
FROM (SELECT a.*
      FROM articulos a
      WHERE cat = '$ArtCat'
      ORDER BY id DESC
      LIMIT 50
     ) a
ORDER BY RAND() 
LIMIT 5;

The key idea is the subquery to get the last 50 entries, and then the final query to get the 5 random rows. The subquery needs to specify how you identify the last 50.

Comments