From: SQL Learner on 6 Feb 2010 16:17 Hi All, Here is a more challenging question derived from my last one in this thread: http://groups.google.com/group/comp.databases.ms-sqlserver/browse_thread/thread/6d105333a6be4865?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 From: Plamen Ratchev on 6 Feb 2010 17:52 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 From: SQL Learner on 6 Feb 2010 19:03 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 From: Plamen Ratchev on 6 Feb 2010 22:26 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 From: SQL Learner on 7 Feb 2010 20:07 Plamen, Thank you for your help again! SQL Learner