CODEWITHSUNDEEP
sqlsql-servergroup-by
deblendewim
deblendewim

Reputation: 425

select record on specific key, if not present, fallback on null value

I have products with pricings and sometimes a company has a more specific price for a product.

I was wondering if I could just have a nice query for this?

Very simple example:

Table ProductPrice:

Id | ProductId | Price | CompanyId
===|===========|=======|===========
1  | 10        | 100   | NULL
2  | 20        | 300   | NULL
3  | 25        | 500   | NULL
4  | 25        | 400   | 977

Now I'd like to do select with always a companyId as parameter, but the results should return the most specific record.

So I'd like to query my ProductPrice table for companyId = 977 and as a result I'd like to have: 

1  | 10        | 100   | NULL
2  | 20        | 300   | NULL
4  | 25        | 400   | 977

Anyone who can show me how this group works?

thanks!

Upvotes: 2

Views: 418

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Hmm. What you want is a prioritization query. Here is one way:

select pp.*
from productprice pp
where pp.company = 977
union all
select pp.*
from productprice pp
where pp.company is null and
      not exists (select 1 from productprice where pp2.productid = pp.productid and pp2.company = 977);

This works (well) for two levels of prioritization. A more general approach is to use row_number():

select pp.*
from (select pp.*,
             row_number() over (partition by productid
                                order by case company 977 then 1 else 2 end
                               ) as seqnum
     from productprice pp
    ) pp
where seqnum = 1;

A third alternative assumes you have a products table (reasonable). In this case:

select pp.*
from product p cross apply
     (select top 1 pp.*
      from productprice pp
      where pp.productid = p.productid
      order by (case companyid when 977 then 1 else 2 end)
     ) pp;

Upvotes: 1

Related Questions