Can I order/group on a column and show a limited number of the results at the top and then show the remainder in a second sort order?

I have a simple table for product data with a column called special which can be 1 or 0, indicating if the product is special and should be highlighted in listings of products that I need to generate.

The original plan was to show all the special records at the top of any SELECT queries I generate. All records are sorted by price, so this results in two groupings of results, first all the special products cheapest to most expensive, the after the last special product, the non special products are listed cheapest to most expensive:

SELECT * FROM table ORDER BY special DESC, price ASC 

Originally the products table had very few special products and the solution above worked fine.

Now however there are a fair number of special products and the solution doesn’t work very well. The biggest annoyance we have is that on the first page of a series of paginated pages of our products, the last special product is very expensive and this is followed by all the remaining products, starting most cheapest. So the “sort by cheapest” order that the page is meant to be following is kind of confusing to the user. Ie:

What I’d like to do – in a single SQL query – is show only a limited number of special products at the top of the results by cheapest first (lets say 3), then show the remaining items (by cheapest first) with the remaining specials appearing in the sort, as if they weren’t specials.

So see the following example and note how after the first 3 records, it’s as if the sort order on ‘special’ doesn’t exist and it’s just sorted on price:

Plan A

The general trick for a 2-part ORDER BY is to have an extra column that says which part the data comes from.

To get some rows from one part and some from the other, use UNION ALL.

( SELECT 1 AS part,
        ...
        FROM t
        WHERE special = 1
        ORDER BY price ASC, id ASC
        LIMIT 3
) UNION ALL (
    SELECT 2 AS part,
           ...
        FROM t
        LEFT JOIN ( SELECT id FROM t ORDER BY price, id LIMIT 3 ) AS t1
        WHERE special = 0
          AND t1.id IS NULL  -- to exclude the specials
) ORDER BY part ASC, price ASC

It gets messier if you don’t want the cheapest 3 specials.

This code should work before MySQL 8.0 / MariaDB 10.2. A “CTE” using a “WITH” of the 3 specials would make the code cleaner in later versions.

You could surround that with another SELECT to get rid of the bogus part column. Or you could be a good guy and use that in your UI to highlight that the first 3 are effectively paid ads.

Plan B allows different sorting orders for the 3 ‘specials’ versus the ‘rest’. The main trick involves GROUP_CONCAT() and FIND_IN_SET().

SELECT all.*
  FROM
    ( SELECT GROUP_CONCAT(id) AS ids
        FROM
        ( SELECT id
            FROM t
            WHERE special = 1
            ORDER BY ...    -- whatever you want
            LIMIT 3
        ) z
    ) AS get_ids,   -- only 1 'row'
    CROSS JOIN
    ( SELECT ...
            FROM t
            ORDER BY 
                FIND_IN_SET(id, ids) = 0, -- do specials first
                FIND_IN_SET(id, ids),     -- order for specials
                price        -- order for non-specials
         ) AS all

This is quite kludgy.

• Get an ordered list of ids.
• Go through entire table
• The ORDER BY is the tricky part; see above
• Works without change if there are no specials.
• Alas, z and ids are clumsy because GROUP_BY does not allow for LIMIT.

Apologies if these do not work when you first try; I am likely to have made some mistakes.