From: SQL Learner on
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


From: SQL Learner on
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

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

>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
>

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
From: Plamen Ratchev on
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
From: SQL Learner on
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