SELECT DISTINCT ModelName FROM tblVehicles
ORDER BY
CASE ModelName WHEN 'Range Rover' THEN 0 ELSE 1 END,
CASE ModelName WHEN 'Discovery' THEN 0 ELSE 1 END,
CASE ModelName WHEN Land Rover' THEN 0 ELSE 1 END,
CASE ModelName WHEN 'Jeep' THEN 0 ELSE 1 END
Again, this will be inefficient, since a table or index scan will be
required. However, with a small number of records, it may perform
acceptably, since a scan will probably be done anyways with a small set of
records.
Bob Barrows
PS. I consider the original solution, the data-driven solution, to be the
more elegant since it can be modified at will without touching any of your
query code.
Pooley wrote:
Hi Bob
Thanks for that. I appreciate what you say (in fact I've already put
Tim's method into practice) but I'm interested to know your other
method.
It's a SQL Server 2000 database.> My table has a field called ModelName which could contain 1 of 5
> vehicle models. I want to force the query to order these in a
> particular order (not alphabetical, but an order that I have
> determined ('Range Rover', 'Discovery', Land Rover', 'Jeep',
> 'Others')
>
> Let's say my current query is:
> "SELECT DISTINCT ModelName FROM tblVehicles"
>
> What do I need to tag on the end to order the result as stated?
>
> Thanks for help!
>
> --