Sorting MySQL results within a resultset

Question

I have a resultset of lets say 10 results. 3 of them have a type 'Pears', and the next 3 have a type 'Apples' and the next three have a type of 'Bananas'. The last record has a type of 'Squeezing Equipment' - unrelated to the fruits. How do I return this set of results (for pagination too) in a GROUPED order that I specify WITHOUT using any inherent sort factor like ALPHABETA or ID etc? I have the all types at my disposal before running the code so they can be specified. i.e.

ID | Bananas
ID | Bananas
ID | Bananas
ID | Apples
ID | Apples
ID | Apples
ID | Pears
ID | Pears
ID | Pears
ID | Squeezing Equipment

Answer 1

This will sort all of the types that are not Squeezing Equipment alphabetically first:

select ID, Type
from MyTable
order by case when Type = 'Squeezing Equipment' then 1 else 0 end, Type

If you have more than one non-fruit type, you can do:

select ID, Type
from MyTable
order by case when Type in ('Squeezing Equipment', 'some other type') then 1 else 0 end, Type

Answer 2

Doesn't a simple ORDER BY and GROUP BY achieve this? Or am I missing something?

SELECT ID, Type FROM TheFruityTable 
GROUP BY Type 
ORDER BY Type;

Answer 3

Use an order by clause. If you want "Squeezing Equipment" to come last use this:

ORDER BY CASE name
    WHEN 'Bananas' THEN 1
    WHEN 'Apples' THEN 2
    WHEN 'Pears' THEN 3
    WHEN 'Squeezing Equipment' THEN 4
    END