Lets say I have a database of widgets. I am showing a list of the top ten groupings of each widget, separated by category.
So lets say I want to show a list of all widgets in category A, but I want to sort them based on the total number of widgets in that category and only show the top 10 groupings.
So, my list might look something like this.
Top groupings in Category A
100 Widgets made by company 1 in 1990.
90 Widgets made by company 1 in 1993.
70 Widgets made by company 3 in 1993.
etc...(for 10 groupings)
This part is easy, but now lets say I want a certain grouping to ALWAYS show up in the listings even if it doesnt actually make the top ten.
Lets say I ALWAYS want to show the number of Widgets made by company 1 in 2009, but I want this grouping to be shown somewhere in my list randomly (not first or last)
So the end list should look something like
Top groupings in Category A
100 Widgets made by company 1 in 1990.
90 Widgets made by company 1 in 1993.
30 Widgets made by company 1 in 2009.
70 Widgets made by company 3 in 1993.
How would i accomplish this in MySQL?
thanks
Edit:
Currently, my query looks like this
SELECT
year,
manufacturer,
MAX(price) AS price,
image_url,
COUNT(id) AS total
FROM
widgets
WHERE
category_id = A
AND
year <> ''
AND
manufacturer <> ''
GROUP BY
category_id,
manufacturer,
year
ORDER BY
total DESC,
price ASC
LIMIT
10
);
Thats without the mandatory grouping in there.
The placement doesnt necessarily have to be random, just shouldnt be on any extreme end. And the list should be 10 groupings including the mandatory listing. So 9 + 1