Hi All,

Here is a more challenging question derived from my last one in this
thread:
http://groups.google.com/group/comp....6be4865?hl=en#

Here is the table:
state source price
NY A 1
NY B 2
NY C 3
CA B 4
CA C 5
TX C 6

How can I show by state the records that satisfied the following
condition:

if A exists then show only the record where source = A,
if A not exists then see if B exists,
If B exists then show only the record where source = B,
if B not exists then see if C exists,
If C exists then show only the record where source = C,
If none of A, B, C exists, then show '---' for all columns except the
state column.

The result in this example should be:

NY A 1
CA B 4
TX C 6

If there is another record as the following:

MA D 7

then the result will be:

NY A 1
CA B 4
TX C 6
MA --- ---

Please help is anyone knows the answer. Thanks.

SQL Learner

Try this:

SELECT CASE WHEN source IN ('A', 'B', 'C')
THEN source
ELSE '---'
END AS source,
CASE WHEN source IN ('A', 'B', 'C')
THEN CAST(price AS VARCHAR(10))
ELSE '---'
END AS price,
state
FROM (
SELECT source, price, state,
ROW_NUMBER() OVER(PARTITION BY state ORDER BY source) AS rk
FROM #TempTable) AS T
WHERE rk = 1;

--
Plamen Ratchev
http://www.SQLStudio.com

Thanks again,Plamen. That is deep!

It seems like the code will not work if the data is changed to this:

Tiger 1 NY
Ape 2 NY
Elephant 3 NY
Ape 4 CA
Elephant 5 CA
Elephant 6 TX

Tiger first, then Ape, then Elephant.

So the result should be:
Tiger 1 NY
Ape 4 CA
Elephant 6 TX


How can we make it work?

SQL Learner

Similar to your other post:

SELECT CASE WHEN source IN ('Tiger', 'Ape', 'Elephant')
THEN source
ELSE '---'
END AS source,
CASE WHEN source IN ('Tiger', 'Ape', 'Elephant')
THEN CAST(price AS VARCHAR(10))
ELSE '---'
END AS price,
state
FROM (
SELECT source, price, state,
ROW_NUMBER() OVER(PARTITION BY state
ORDER BY CASE source
WHEN 'Tiger' THEN 1
WHEN 'Ape' THEN 2
WHEN 'Elephant' THEN 3
ELSE 4
END) AS rk
FROM #TempTable) AS T
WHERE rk = 1;

--
Plamen Ratchev
http://www.SQLStudio.com

Plamen,

Thank you for your help again!

SQL Learner