CODEWITHSUNDEEP
mysqlsqldatabase
Jesús Cerezuela Zaplana
Jesús Cerezuela Zaplana

Reputation: 129

Mysql: obtaining n elements of a given subset of N records

my goal is to get the n "best" items from a given subset of N registers of a table. I have a table called recipes, and all I want to get the 5 best recipes (the most liked) of the last, let's say, 50 recipes uploaded. Only for clarifying, it would be something like this:

SELECT * FROM `recipes` 
WHERE id IN 
    (SELECT id FROM `recipes` ORDER BY created DESC LIMIT 50) 
ORDER BY likes DESC LIMIT 5

But LIMIT can't be used in a subquery. So, what's the simplest way to achieve that? Thanks in advance.

Upvotes: 0

Views: 40

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

What you want to do can be done without a join:

SELECT r.*
FROM (SELECT r.*
      FROM recipes
      ORDER BY created DESC
      LIMIT 50 
     ) r
ORDER BY likes DESC
LIMIT 5;

Upvotes: 1

radar
radar

Reputation: 13425

You can do the same with a join

  SELECT * FROM `recipes`  R1
 JOIN 
(SELECT id FROM `recipes`      ORDER BY created DESC LIMIT 50) R2
 ON R1.id =R2.id
 ORDER BY R1.likes DESC LIMIT 5

Upvotes: 1

Related Questions