SQL - Conditional Where statement

Question

I have the following table and data:

DECLARE @Temp TABLE (id int, city varchar(10), ref varchar(10))

INSERT INTO @Temp (id, city, ref)
SELECT 1, 'London', 'GBP' UNION ALL
SELECT 2, 'London', 'EUR' 

SELECT  *
FROM    @Temp
WHERE ((city = 'London' AND ref = 'GBP') OR (city = 'London' AND ref <> 'GBP'))

This returns:

1 London GBP
2 London EUR

What I would like to do is to check for where city = 'London' AND ref = 'GBP' first, if there is no row qualifying this then check if the city is London and ref can be anything, tried using case when but did not quite work.

In this scenario I should only get the row #1.

Thanks

xlecoustillier · Accepted Answer · 2015-10-23 08:45:55Z

1

Just return the rows not containing GBP when there are no GPB rows, using NOT EXISTS:

SELECT  *
FROM    @Temp
WHERE (city = 'London' AND ref = 'GBP') OR (city = 'London' AND NOT EXISTS (SELECT  *
            FROM    @Temp
            WHERE city = 'London' AND ref = 'GBP'))

See this fiddle

answered Oct 23, 2015 at 8:45

xlecoustillier

16.4k15 gold badges64 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Arvo · Accepted Answer · 2015-10-23 08:46:55Z

1

If you are dealing with single record, then next should work:

SELECT  TOP 1 *
FROM    @Temp
WHERE city = 'London'
ORDER BY CASE WHEN ref = 'GBP' THEN 0 ELSE 1 END

answered Oct 23, 2015 at 8:46

Arvo

10.6k1 gold badge34 silver badges36 bronze badges

1 Comment

Arvo Over a year ago

TOP 1 by default doesn't return ties. OP problem description is not very clear, I assumed that he always wants to return only single row.

qxg · Accepted Answer · 2015-10-23 08:48:29Z

1

SELECT *
FROM @Temp
WHERE city = 'London' AND ref = 'GBP'
UNION
SELECT *
FROM @Temp
WHERE NOT EXISTS (
    SELECT * 
    FROM @Temp 
    WHERE city = 'London' AND ref = 'GBP'
) AND city = 'London'

SELECT after UNION is conditional where.

answered Oct 23, 2015 at 8:48

qxg

7,0961 gold badge31 silver badges37 bronze badges

Comments

Lukasz Szozda · Accepted Answer · 2015-10-23 08:48:39Z

1

You can use CASE WHEN:

SELECT  *
FROM @Temp
WHERE 
  CASE
   WHEN city = 'London' AND ref = 'GBP' THEN 1
   WHEN city = 'London' AND ref <> 'GBP' AND NOT EXISTS(SELECT 1 FROM @Temp
                                                        WHERE city = 'London' 
                                                        AND ref = 'GBP') THEN 1
   ELSE 0
END = 1

LiveDemo

answered Oct 23, 2015 at 8:48

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Comments

jarlh · Accepted Answer · 2015-10-23 08:44:54Z

0

select city, max(ref)
from @Temp
group by city

Alternatively

select city, ref
from @Temp t1
where t1.ref = 'GBP'
   or not exists (select 1 from @Temp t2
                  where t2.city = t1.city
                    and t2.ref = 'GBP')

answered Oct 23, 2015 at 8:44

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Comments

Eric · Accepted Answer · 2015-10-23 08:55:16Z

0

I think you can deal with ordering in the case like this

SELECT TOP 1 *
FROM @Temp t 
    CROSS APPLY -- The ordering logic write here
    (
        SELECT
            CASE 
                WHEN (city = 'London' AND ref = 'GBP') THEN 1
                WHEN (city = 'London' AND ref <> 'GBP') THEN 2
                -- Not match at all
            END AS Value
    ) orders
WHERE orders.Value IS NOT NULL
ORDER BY 
    CASE WHEN orders.Value IS NULL THEN 1 ELSE 0 END, 
    orders.Value

answered Oct 23, 2015 at 8:55

Eric

5,74319 silver badges26 bronze badges

Comments

Stanislovas Kalašnikovas · Accepted Answer · 2015-10-23 08:55:17Z

0

You can try with NOT EXISTS something like:

SELECT  *
FROM    @Temp
WHERE ((city = 'London' AND ref = 'GBP') OR (city = 'London' AND ref <> 'GBP'
      AND NOT EXISTS (SELECT *
                      FROM  @Temp
                      WHERE city = 'London' AND ref = 'GBP')))

edited Oct 23, 2015 at 8:55

answered Oct 23, 2015 at 8:48

Stanislovas Kalašnikovas

9,0735 gold badges36 silver badges60 bronze badges

2 Comments

xlecoustillier Over a year ago

Your second solution doesn't work: ordering by id doesn't guarantee that the GBP will be the first row (in this case yes, but this certainly won't be the case everytime).

xlecoustillier Over a year ago

Same with ref, what if some other currency exists named, say, USD ?

Collectives™ on Stack Overflow

SQL - Conditional Where statement

7 Answers 7

Comments

1 Comment

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

1 Comment

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related