specify a non-linear order by

[Alpine7]

How can I order the results of my query in non-linear fasion. I have a
field with these values: Reg S, 144A, US and want to order my results
by US, 144A, Reg S.
I would prefer not to create another field in the table if possible.

[gfrizzle]

You can do it like this:

....ORDER BY CASE fieldname WHEN 'US' THEN 1 WHEN '144A' THEN 2 WHEN
'Reg S' THEN 3 ELSE 4 END

Alpine7 wrote:

How can I order the results of my query in non-linear fasion. I have a field with these values: Reg S, 144A, US and want to order my results
by US, 144A, Reg S.
I would prefer not to create another field in the table if possible.

[Alpine7]

Thanks that is exactly what I was looking for works great.

[--CELKO--]

SELECT ..
CHAR_INDEX ('US, 144A, Reg S.') AS sort_col
FROM Foobar
ORDER BY sort_col;

[Steve Jorgensen]

I would point out that, whenever you are repeating a text value in a column of
a table, you should probably be using a surrogate key to a lookup table
instead. Your new requirement for a specific sort order for the codes drives
this point home. If you create a lookup table for your codes, you can add a
sort order attribute to the codes table and not be forced to choose between
adding another column to your main table or using SQL that will be invalid the
next time a code is added.

Example:

paper_type
----------
(pk) paper_type_id int identity
(u) paper_type_code varchar(10)
order_sequence int

document
--------
(pk) document_id
(fk) paper_type_id
document_title

To query from these tables in the document type sort order, ...

SELECT
document.docume nt_title,
paper_type.pape r_type_code
FROM
document
INNER JOIN paper_type
ON document.paper_ type_id = paper_type.pape r_type_id
ORDER BY
paper_type.orde r_sequence
document.docume nt_title

On 9 Dec 2004 07:53:16 -0800, "Alpine7" <da****@datasho ck.com> wrote:

How can I order the results of my query in non-linear fasion. I have a
field with these values: Reg S, 144A, US and want to order my results
by US, 144A, Reg S.
I would prefer not to create another field in the table if possible.