hierarchical selection within a select statment

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

Re: hierarchical selection within a select statment

(rshivaraman@gmail.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@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: hierarchical selection within a select statment

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

-RS