Need help with a SQL statement

[TheBionicOrange]

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.

[Al42]

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

[TheBionicOrange]

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.

[BruceG]

you can use a CASE expression:

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 ..

[szlamany]

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...

[TheBionicOrange]

OK great.

Thank you both