Looking for SQL-Server function to identify min/max row and extract information from these rows

Question

Imagine data in this format:

Customer, Object, Price
[William, Orange, 100p
William, Apple, 80p
William, Banana 60p
Casper, Cola, 150p
Casper, Beer, 120p
Casper, Peanuts, 200p]

I am interested in extracting the most and least expensive purchase for every buyer, along with the actual item.

The output should look like this.

Customer, MostExpItem, MostExpCost, LeastExpItem, LeastExpCost
William, Orange, 100p, Banana, 60p
Casper, Peanuts, 200p, Beer, 120p

Any pointers? I can use group by to easily extract the min/max values. Is a subquery required for this problem?

Duplictates won't be an issue for Data I am working on! Thought I can see how that is not obvious from the example. — Blackrunner01, Mar 30 '20 at 00:29

score 0 · Accepted Answer · answered Mar 29 '20 at 23:51

You can use window functions:

select
    customer,
    max(object) filter(where price = most_exp_price) most_exp_item,
    most_exp_price,
    max(object) filter(where price = less_exp_price) less_exp_item,
    less_exp_price
from (
    select 
        t.*, 
        max(price) over(partition by customer) most_exp_price,
        min(price) over(partition by customer) less_exp_price
    from mytable t
) t
where price in (most_exp_price, less_exp_price)
group by customer

The subquery comptes the maximum and minimum price per customer. The outer query filters on the top and bottom price, aggregates by customer and displays the corresponding items.

Thank you very much! Would you think this is faster/slower or equal to the response posted by Gordon Linoff. I am pretty new at SQL and I've got huge database to work on so any insight is appreciated. — Blackrunner01, Mar 30 '20 at 00:01

score 0 · Answer 2 · answered Mar 29 '20 at 23:58

0

In Postgres, you could make use of arrays if you wanted to avoid subqueries:

select customer,
       min(price),
       (array_agg(order by price))[1],
       max(price),
       (array_agg(order by price desc))[1]
from t
group by customer;

answered Mar 29 '20 at 23:58

Gordon Linoff

1,242,037
58
646
786

Looking for SQL-Server function to identify min/max row and extract information from these rows

2 Answers2