How would I do this in MySQL?

Question

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

Answer 1

I would use an UNION query: your current query union the query for 2009, then handle the sorting in the presentation layer.

Answer 2

You can write 2 separate query (one for all companies and another just for company 1) and then use UNION to join them together. Finally, add ORDER BY RAND(). It will look like

SELECT * FROM
(
   SELECT company_id, company_name, year, count(*)  as num_widgets 
   .... 
   LIMIT 10
   UNION DISTINCT
   SELECT company_id, company_name, year, count(*)  as num_widgets 
   ...
   WHERE company_id =1      
   ...
   LIMIT 10
)x
ORDER BY RAND();

Answer 3

You could add a field that you make true for company 1 in 2009 and include it in the where clause. Something like

select * from companies where group = 'some group' or included = true order by included, widgets_made limit 10

For the random part you would have that as subquery then include a column that has a random number from 1 to 10 if the field that you made is true, and rownum otherwise, then sort by that column