Dan Nelson wrote:
> --snip--
>> More efficient would be to use the FIELD function:
>>
>> SELECT * FROM team ORDER BY FIELD(office,"Exec","VP","Dir");
>>
>
> Oops. I only read the replies and not the original post. Assuming
> there are many other values for the "office" field, you might want
>
> SELECT * FROM team WHERE office = 'Exec' UNION
> SELECT * FROM team WHERE office = 'VP' UNION
> SELECT * FROM team WHERE office = 'Dir';
>
Indeed! :) Especially if the table is of any decent size (I assumed that
it was not *huge*). The UNION will give index accesses, the "tricks" on
the ORDER BY will cause filesorts (although, they will still likely be
in memory unless you the table is large, again).
You should of course EXPLAIN and tune accordingly (with response times
as well).
Cheers,
Mark
--
Mark Leith, Support Engineer
MySQL AB, Worcester, England, www.mysql.com
Are you MySQL certified? www.mysql.com/certification
