Problem With Case Function

Hi! Guys
i have problem with order by clause.
i have table some T1 with two columns say Itemname and Status
and data like this
ItemName Status
T1 G
T2 D
T3 US
T4 S
T5 G
T6 NULL
T7 NULL
Now i want to show with status like
G
D
S
US
SELECT
CASE Status
WHEN 'G' THEN 0
WHEN 'NULL' THEN 0
WHEN 'D' THEN 1
WHEN 'S' THEN 2
WHEN 'US' THEN 3 END AS STATUSORDER
FROM T1
But it is not ececuting properly.it is not exact answer what i want.
plz , give solutionHi, shiva
You can use one of these queries:
SELECT
CASE
WHEN Status='G' THEN 0
WHEN Status IS NULL THEN 0
WHEN Status='D' THEN 1
WHEN Status='S' THEN 2
WHEN Status='US' THEN 3
END AS STATUSORDER
FROM T1
or:
SELECT
CASE Status
WHEN 'D' THEN 1
WHEN 'S' THEN 2
WHEN 'US' THEN 3
ELSE 0
END AS STATUSORDER
FROM T1
Razvan|||What does it mean "not properly" ?
CREATE TABLE #Test
(
col VARCHAR(2) NULL
)
INSERT INTO #Test VALUES ('G')
INSERT INTO #Test VALUES ('D')
INSERT INTO #Test VALUES ('US')
INSERT INTO #Test VALUES ('S')
INSERT INTO #Test VALUES ('G')
INSERT INTO #Test VALUES (null)
INSERT INTO #Test VALUES (null)
SELECT col FROM #TesT WHERE col IS NOT NULL
GROUP BY col
ORDER BY
CASE col
WHEN 'G' THEN 0
WHEN 'D' THEN 1
WHEN 'S' THEN 2
WHEN 'US' THEN 3 END
"shiva" <bany.shanker@.gmail.com> wrote in message
news:1133867516.797293.72880@.f14g2000cwb.googlegroups.com...
> Hi! Guys
>
> i have problem with order by clause.
>
> i have table some T1 with two columns say Itemname and Status
>
> and data like this
>
> ItemName Status
> T1 G
> T2 D
> T3 US
> T4 S
> T5 G
> T6 NULL
> T7 NULL
> Now i want to show with status like
> G
> D
> S
> US
> SELECT
> CASE Status
> WHEN 'G' THEN 0
> WHEN 'NULL' THEN 0
> WHEN 'D' THEN 1
> WHEN 'S' THEN 2
> WHEN 'US' THEN 3 END AS STATUSORDER
> FROM T1
> But it is not ececuting properly.it is not exact answer what i want.
> plz , give solution
>|||thanks Razvan,
it's working fine.
thanks a lot.