Need help with a SQL statement

This SQL statement is in a VB prog, so thats why I am posting it here.
Nothing to do with the fact that most users commute here so I am more likely to get it answered quickly ... honest :)

OK then ... I have a statement that grabs a load of records from a table.
One of the fields is known as "Risk Indicator" and is stored as either "R", "A" or "G", symbolising Red, Amber or Green.

I want to bring the recordset back in the order of (R)ed, (A)mber, and then (G)reen, which obviously leads to problems as a string sort in either ascending or descending format will not give me what I need.

My question is ... is there a way of transposing the "R", "A" and "G" values IN the select statement, so they could be something like "A", "B" and "C" ?
That way I would have my order as I need it.

I want to do it in the SQL statement if I possibly can, so I don't have to faff about afterwards, sorting my results etc.

Thanks.

Give them numeric values, 1 (Red) 2 (Amber) and 3 (Green). Then, in your program, display the color names for those numbers.

Yes thats kinda my point ... HOW do I do that in a SQL statement ?
By the way .. I'm using SQL Server 2000 before anyone asks.

you can use a CASE expression:

Code:

---

SELECT field1
        , field2
        , the_code,
        , case the_code
                  when 'R' then 'A'
                  when 'A' then 'B'
                  when 'G' then 'C'
                  else '?'
            end AS sort_code
FROM my_table etc.
ORDER BY 4

---

Note that if you want to sort by the transformed value, the easiest way to do it is by using the position of the expression in the ORDER BY clause - otherwise you'd have to specify that whole expression again in the ORDER BY clause (i.e. ORDER BY case the_code when ... etc.) - you CANNOT say ORDER BY sort_code (that would be too easy .. :)

Belongs in the DB forum ;)

At any rate - if you are going to be doing this a lot and if you already have a "lookup/description" table that changes the A to Amber - then also add a column to that table for SortOrder - make it an INT field - put a 1, 2 or 3 into it. JOIN to that table and ORDER BY SortOrder.

That's the answer from the DB-side of the forum...

OK great.

Thank you both :)