Quick query question

[Pooley]

Hi

How do I force the order of a SQL query?

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!

--

 

[Tim]

add another numeric field called neworder and use that



Tim

 

[Pooley]

Sure. Is that the only way to do it? Just trying to be 'elegant' about it

--

add another numeric field called neworder and use that



Tim

 

[Bob Barrows]

That is not only the "elegant" way to do it, it is also the most efficient
way to do it, since you can put an index on the column and get your results
very quickly.

There is another way, but it depends on knowing what your database is (type
and version)

HTH,
Bob Barros

 

[Pooley]

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.

 

[Bob Barrows]

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.