Thanks!!

Also thought of another way (which is much the same..)

select fielda,fieldb, Test = case when fielda = 'red' then 1 else 0 end
 from tablea
order by test desc, fieldb

-----Original Message-----
From: Brian Freeman [mailto:freeman@C...]
Sent: 13 March 2003 14:46
To: sql language
Subject: [sql_language] RE: Different Kind of SQL Sort


select fielda,fieldb from tablea
order by case fielda
			when 'Red' then ' Red'
			else fielda
	   end, fieldb

The idea here is putting a space in front ascii 32 which should be before
other values (letters start with ascii 64).  alternately add a tab character
(ascii value 10) or similar.

Brian Freeman
(xxx) xxx-xxxx  ext. 415
Carnegie Technologies/Bluewave Computing 
www.carnegie.com and www.bluewave-computing.com

-----Original Message-----
From: Niall Hannon (ext. 772) [mailto:Niall.Hannon@f...]
Sent: Thursday, March 13, 2003 9:24 AM
To: sql language
Subject: [sql_language] Different Kind of SQL Sort


Hi,

Is it possible to do a sort in sql beyond the typical field1 asc/desc etc.
(Could do it programmitcally I suppose but prefer SQL solution)
For example lets say I have this table

Fielda Fieldb
Red	20
Red	30
Blue	10
Blue	20
Blue	30
White	10
Red	10

I want to sort a query based on the values in Fielda to look like this
Fielda Fieldb
Red	10
Red	20
Red	30
Blue	10
Blue	20
Blue	30
White	10

So I want to ensure the "Red" is at the top of the list and then a normal
asc sort on the rest of the values in Fielda and Fieldb.

In English I mean something like "order by Fielda but order by value "Red"
first and then normal asc after that"

Any ideas?
Thanks
Niall





**************************************************************************
The information contained in this e-mail is confidential,
may be privileged and is intended only for the use of the
recipient named above. If you are not the intended
recipient or a representative of the intended recipient,
you have received this e-mail in error and must not copy,
use or disclose the contents of this email to anybody
else. If you have received this e-mail in error, please
notify the sender immediately by return e-mail and
permanently delete the copy you received. This email has
been swept for computer viruses. However, you should
carry out your own virus checks.


Registered in Ireland, No. 205721. http://www.FINEOS.com
**************************************************************************