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|