PostgreSQL - Select row with composite maximum value from 2 columns

Question

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.

user330315 · Accepted Answer · 2018-05-30 08:52:23Z

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

answered May 30, 2018 at 8:52

user330315

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

1 Comment

Gordon Linoff Over a year ago

As a note: The third condition can be simplified to (benefit_type = 'All')::int desc.

Collectives™ on Stack Overflow

PostgreSQL - Select row with composite maximum value from 2 columns

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related