SQL Query - ORDER BY - please hep?

[Guest_imported]

Hi Guys

I was wondering whether it is possible to do the following in an SQL query.
I do not want to break the query out into a Stored Procedure or Add a new field to my dB for the "Order By".

Say I have 5 results:

1. Opel
2. Ford
3. Toyota
4. Audi
5. Volkswagen

I would like to ORDER them by NAME ASC but want Volkswagen to be the first record and then rest to be in Alphabetical.
eg.

5. Volkswagen
4. Audi
2. Ford
1. Opel
3. Toyota

Is this possible in a "select NAME from TABLE order by NAME" query?

Thanks for your help.
Regards
Col

 

[karluk]

Your question is very similar to thread183-340856. Please read it for several suggestions on how to implement a customized sort order.

 

[Sqlsa]

Hi ,
Try this...use UNION

Select NAME from TABLE where NAME like 'Volksvagen'
UNION
Select NAME from TABLE where NAME not in ('Volksvagen')
ORDER BY NAME


Hope it helps.

Sreenivas
avnsr@hotmail.com
-----------------

 

[r937]

sreenivas, you forgot to distinguish the rows coming from the two selects in the union, they're going to sort together just as before

try this instead --

select 1 as rectype
, carname
from cartable
where carname='Volkswagen'
union all
select 2 as rectype
, carname
from cartable
where carname<>'Volkswagen'
order by
rectype
, carname

note: UNION ALL instead of UNION, to avoid the unnecessary sort to detect duplicate result rows

alternatively, as suggested on that other thread,

select case
when carname='Volkswagen'
then 1 else 2
end as rectype
, carname
from cartable
order by
rectype
, carname

rudy

http://rudy.ca/

 

[Sqlsa]

hi rudy,

oops..that was a mistake...sorry.

thanks for correcting me.

Sreenivas

 

[SteveMe]

I do realize that you stated that you have 5 occurences. But in the situation where you may have a ridiculous amount of occurences it might be best to create a table that is basically your sort manipulation.

You'd have 2 columns possibly more. Your primary key would be on your code field (Opel, Ford, Toyota) etc
Opel -- 2
Ford -- 3
Toyota -- 4
Audi -- 5
Volkswagen -- 1

and so on and so on

 

[Guest_imported]

Thanks alot guys! Works like a charm.
The CarType field will be dynamic, so I may have up to 20 differant results, but that shouldnt have any serious impact on performance.

Thanks again.
Regards,
Col