MySQL - Sort the FIRST 3 rows only

Question

My client wants a search on his wordpress website that shows the 3 first results from a specific category (premium products) and then sort the rest of the results by relevance.

It needs to be something like this:

Search: green apple

1. green pineapple (premium)
2. red apple (premium)
3. apple pie (premium)
4. green apple
5. big green apple
6. small green apple

I'm thinking if there is something that counts the selected rows to then create a OrderBy condition like this below:

SELECT * 
FROM `posts` 
ORDER BY ( 
    CASE WHEN `posts.category` LIKE 'premium' AND COUNT(ROW) < 4 
        THEN 0 
        ELSE 1 
    END ), 
    `posts.post_date` DESC

Obs.: The rest of the search (after the first 3 premiums) needs to select literally everything that matches the search, it means that it can select premium posts as well, so I can't use "NOT LIKE premium".

Answer 1

I would use a UNION query to select 3 premium products and then the rest:

SELECT * 
FROM ((SELECT 1 AS premium, posts.* 
       FROM posts
       WHERE `posts.category` LIKE 'premium'
       ORDER BY post_date_desc
       LIMIT 3)
      UNION
      (SELECT 0 AS premium, posts.*
       FROM posts)
     ) p
GROUP BY p.ID
ORDER BY premium DESC, `p.post_date` DESC

UNION by default removes duplicates so it will prevent the same post repeating in the list.

Answer 2

You could just UNION two queries, like:

(SELECT p.*, 1 is_premium FROM posts p WHERE category = 'premium' ORDER BY post_date DESC LIMIT 3)
UNION
(SELECT p.*, category = 'premium' FROM posts)
ORDER BY is_premium, post_date DESC

The first query selects the 3 most recent premium posts. The second query selects all posts. UNION takes care of removing duplicates accross queries. The outer query puts premium posts first, and orders by descending date.