Posted by Erland Sommarskog on 06/07/07 21:32

(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/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]