1

I would like to select the best offers for a merchant in PostgreSQL 9.6 according some simple rules:

  • An offer is better than another if its discount value is greater regardless the benefit type
  • If the discount values are equal, then the one with benefit type ALL beats the one with FOOD
  • If both discount and benefit type are the same, then any offer can be selected e.g. pick the first one

So best is not just a max() call but a "conditional" max() where another column should be inspected too to determine which row it is.

Could you please help?

Schema:

create table offer (
    id bigserial not null,
    discount int4,
    benefit_type varchar(25),
    ...
    merchant_id int8 not null
);

Query (partial):

select merchant_id, max(discount) as max_discount
from offer
group by merchant_id;

Sample offers in DB:

id  discount    benefit_type    ... merchant_id
0   10          FOOD                0
1   20          FOOD                0
2   20          ALL                 0
3   30          ALL                 1
4   40          ALL                 1
5   40          FOOD                1
6   40          ALL                 2
7   50          FOOD                2

Desired result set:

merchant_id     max_discount    benefit_type
0               20              ALL
1               40              ALL
2               50              FOOD
  • Merchant 0's best offer is offer 2 because 20 ALL > 20 FOOD.
  • Merchant 1's best offer is offer 4 because 40 ALL > 30 ALL.
  • Merchant 2's best offer is offer 5 because 50 FOOD > 40 ALL.
Andras Szoke
  • 125
  • 1
  • 7

1 Answers1

4

This can be achieved using distinct on() and a custom sort definition for the benefit_type:

select distinct on (merchant_id) *
from offer
order by merchant_id, 
         discount desc, 
         case when benefit_type = 'ALL' then 1 else 2 end;

This prefers higher discount. If two discounts are the same, a benefit_type of ALL is used as the tie-breaker.

Online example: http://rextester.com/TFBP17217