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:
|23||penultimate special product||1219.99||1|
|22||last special product||1630.99||1|
|21||first non special product||1.99||0|
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:
|41||a special product||12.99||1|
|22||penultimate special product||13.99||1|
|13||last special product due to limit of 3 at top of results||14.99||1|
|434||a normal product||8.49||0|
|545||another normal product||11.99||0|
|16||a special product||15.99||1|
|1216||not a special product||16.99||0|
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
( 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
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
ORDER BYis the tricky part; see above
- Works without change if there are no specials.
idsare clumsy because
GROUP_BYdoes not allow for
Apologies if these do not work when you first try; I am likely to have made some mistakes.