hierarchical selection within a select statment

[rshivaraman@gmail.com]

CREATE TABLE RS_A(ColA char(1), ColB varchar(10))

INSERT INTO RS_A
VALUES ('S', 'shakespeare')
INSERT INTO RS_A
VALUES ('B', 'shakespeare')
INSERT INTO RS_A
VALUES ('P', 'shakespeare')

INSERT INTO RS_A
VALUES ('S', 'milton')
INSERT INTO RS_A
VALUES ('P', 'milton')
INSERT INTO RS_A
VALUES ('B', 'shelley')

INSERT INTO RS_A
VALUES ('B', 'kafka')
INSERT INTO RS_A
VALUES ('S', 'kafka')

INSERT INTO RS_A
VALUES ('P', 'tennyson')


SELECT * FROM RS_A

Now i need a select which selects based on hierarchy

if ColA = 'S', then select only that row
else if ColA = 'B' then select only that row
else if colA = 'P' then select only that row

So my results should look like
S shakespeare
S milton
B shelley
S kafka
P tennyson

Is there a way to do this within a select statement
I tried using a CASE in WHERE CLAUSE but it put out all rows which
existed/

If any of you can help me with this right away, its is greatly
appreciated
Thanks in advance

[Erland Sommarskog]

Re: hierarchical selection within a select statment

(rshivaraman@gm ail.com) writes:

SELECT * FROM RS_A
>
Now i need a select which selects based on hierarchy
>
if ColA = 'S', then select only that row
else if ColA = 'B' then select only that row
else if colA = 'P' then select only that row
>
So my results should look like
S shakespeare
S milton
B shelley
S kafka
P tennyson
>
Is there a way to do this within a select statement
I tried using a CASE in WHERE CLAUSE but it put out all rows which
existed/

First translate the codes to numeric values with CASE, you can take
MIN, and then translate back:

SELECT CASE minval WHEN 1 THEN 'S' WHEN 2 THEN 'B' WHEN 3 THEN 'P' END,
ColB
FROM (SELECT ColB, minval = MIN(CASE ColA
WHEN 'S' THEN 1
WHEN 'B' THEN 2
WHEN 'P' THEN 3
END)
FROM RS_A
GROUP BY ColB) AS x

If there are many possible values for ColA, it would be better to
put the mapping in a table and then join with that table.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[rshivaraman@gmail.com]

Re: hierarchical selection within a select statment

Ingenius :
Thank you for the above and the RETURN was what was missing after
RAISEERROR

-RS