Hi All,

Hope someone can help to solve this SQL question.

I have the following table called '#temptable' (it is a temp table)

source price state
A 1 NY
B 2 NY
C 3 NY
B 4 CA
C 5 CA
C 6 TX

I need a SQL statement that performs the following pesudo code:

if source A exists then (select * from #temptable where source = 'A')
if source A not exists then check if source B exist.
if source B exists then (select * from #temptable where source = 'B')
if source B not exists then check if source C exist.
if source C exists then (select * from #temptable where source = 'C')
if source C not exists then print 'Nothing is found.'

The following code can be used to create the temp table for testing:

create table #TempTable (source char(1), price int, state char (2))
insert into #TempTable values ('A', 1, 'NY')
insert into #TempTable values ('B', 2, 'NY')
insert into #TempTable values ('C', 3, 'NY')
insert into #TempTable values ('B', 4, 'CA')
insert into #TempTable values ('C', 5, 'CA')
insert into #TempTable values ('C',6, 'TX')

Thanks.

SQL Learner

I did some experiment with the code and found the solution:

================================================
if exists
( select * from #TempTable where source = 'A')
select * from #TempTable where source = 'A'
else if exists
( select * from #TempTable where source = 'B')
select * from #TempTable where source = 'B'
else if exists
( select * from #TempTable where source = 'C')
select * from #TempTable where source = 'C'
else
print 'Nothing is found.'
================================================

However, I know there are different approaches to this problem. If
anyone know of different ways (probably more advanced), please share.
Thanks.

SQL Learner

On Sat, 6 Feb 2010 11:43:38 -0800 (PST), SQL Learner wrote:

Hi SQL Learner,

How about:

SELECT source, price, state
FROM #TempTable
WHERE source = (SELECT MIN(source) FROM #TempTable);

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

This should do it:

SELECT TOP 1 WITH TIES source, price, state
FROM #TempTable
WHERE source IN ('A', 'B', 'C')
ORDER BY source;

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

Hi Hugo and Plamen,

Thank you guys for your solution.

What if I change the data to this:

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

Just substitute Tiger for A, Ape for B, and Elephant for C. This way,
the Top 1 and Min methods will not work, right?
How can we modify the code to accommendate for this change?

Thank you in advanced.

SQL Learner

SELECT TOP 1 WITH TIES source, price, state
FROM #TempTable
WHERE source IN ('Tiger', 'Ape', 'Elephant')
ORDER BY CASE source
WHEN 'Tiger' THEN 1
WHEN 'Ape' THEN 2
WHEN 'Elephant' THEN 3
END;

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

Plamen,

This is cool. Thanks.

Could you or anyone explain how the following code work?

ORDER BY CASE source
WHEN 'Tiger' THEN 1
WHEN 'Ape' THEN 2
WHEN 'Elephant' THEN 3
END;

Thanks again.

Anthony

The CASE expression does mapping of the sources to sort values, in a way giving priority to 1, then 2, 3, etc. That way
the source with sort value 1 (Tiger) is on top of the list if any entries exist. If not then the next one.

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

Thanks again,Plamen!

SQL Learner