how to select rows based on condition sql

Question

I have table which contains the ids like -

id
o1
o2
o3

now another table contains price for these ids based on the country like

id    country     price
o1    IND         11
o1    US          10
o1    default     9
o2    default     2
o3    UK          4
o3    default     3

basically i have to write a stored procedure where i will pass the region as parameter. for example if region is UK then i want select rows as follows if there is match then select the row otherwise default row.

id       country        price
o1       default        9
o2       default        2
o3       UK             4

Can you please suggest a good and efficient way of writing in SQL?

Felix Pamittan · Accepted Answer · 2016-04-06 00:13:37Z

1

Use CROSS APPLY with TOP:

DECLARE @country VARCHAR(20) = 'UK'

SELECT  x.*
FROM tblId i
CROSS APPLY(
    SELECT TOP 1 *
    FROM tblPrice p
    WHERE
        p.id = i.id
        AND (p.country = @country OR p.country = 'default')
    ORDER BY
        CASE 
            WHEN p.country = 'default' THEN 1 
            ELSE 0
        END
)x

answered Apr 6, 2016 at 0:13

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2016-04-06 00:13:08Z

0

One method uses two left joins:

select ids.id, coalesce(p.country, d.country) as country,
       coalesce(p.price, d.price) as price
from ids left join
     prices p
     on ids.id = p.id and p.country = @country left join
     prices d
     on d.country = 'default';

I think a more interesting method uses cross apply:

select p.*
from ids cross apply
     (select top 1 p.*
      from prices p
      where p.id = ids.id and p.country in (@country, 'default')
      order by (case when p.country = @country then 1 else 2 end)
     ) p;

answered Apr 6, 2016 at 0:13

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

how to select rows based on condition sql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related