473,320 Members | 1,961 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Quick query question

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!

--
Jul 19 '05 #1
5 1497
Tim
add another numeric field called neworder and use that

Tim

"Pooley" <po****@here.com> wrote in message
news:uK*************@tk2msftngp13.phx.gbl...
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!

--

Jul 19 '05 #2
Sure. Is that the only way to do it? Just trying to be 'elegant' about it ;)

--

"Tim" <me@nottellingyou.com> wrote in message
news:eL**************@TK2MSFTNGP11.phx.gbl...
add another numeric field called neworder and use that

Tim

"Pooley" <po****@here.com> wrote in message
news:uK*************@tk2msftngp13.phx.gbl...
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!

--


Jul 19 '05 #3
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 wrote:
Sure. Is that the only way to do it? Just trying to be 'elegant'
about it ;)
"Tim" <me@nottellingyou.com> wrote in message
news:eL**************@TK2MSFTNGP11.phx.gbl...
add another numeric field called neworder and use that

Tim

"Pooley" <po****@here.com> wrote in message
news:uK*************@tk2msftngp13.phx.gbl...
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!

--

Jul 19 '05 #4
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.

--
Pooley
"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:OC****************@TK2MSFTNGP10.phx.gbl...
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 wrote:
Sure. Is that the only way to do it? Just trying to be 'elegant'
about it ;)
"Tim" <me@nottellingyou.com> wrote in message
news:eL**************@TK2MSFTNGP11.phx.gbl...
add another numeric field called neworder and use that

Tim

"Pooley" <po****@here.com> wrote in message
news:uK*************@tk2msftngp13.phx.gbl...
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!

--


Jul 19 '05 #5
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!
>
> --


Jul 19 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.