I want to show you my problem by this simple example.

I already have this SQL result of customers buying either rice or melon. If a customer have entries for both products, I only want to print out the data row for melon.

So this must turn into...

THIS...

I already tried a query something like this.

SELECT 
CustomerId
Name
MIN(Product)
MIN(Price)

FROM LIST
GROUP BY CustomerId,Name
HAVING Product = 'Melon'

I am aware that this will never work, because I also want to see the rice entries, if they didnt bought any melons. How can I filter this list. Maybe with a case condition inside the having?

Thank you in advance.

CodePudding user response：

One way of thinking about it is you have a preference order to these items, so we can rank them and return only the first, if we have ranking window functions available to us:

WITH customers AS (
  SELECT 
    CustomerId,
    name,
    product,
    price,
    RANK() OVER (PARTITION BY CustomerId) ORDER BY (
        CASE WHEN Product = 'Melon' THEN 1 
             WHEN Product = 'Rice' THEN 2
        ...
        END
    ) AS product_rank
  FROM LIST
  GROUP BY CustomerId,Name
)
SELECT 
  CustomerId,
  name,
  product,
  price 
FROM customers
WHERE product_rank = 1;

Hope that helps.

CodePudding user response：

Row_number the rows as needed and take the first one. 'Melon' < 'Rice' so just order by Product . I'm using a handy top(1) with ties of Sql Server

select top(1) with ties
    CustomerId,
    Name,
    Product,
    Price
from LIST
order by row_number() over(partition by CustomerId order by Product)

CodePudding user response：

Here's a working query. Not the most optimal one but i guess your data is not big so it'll do the job. Test1 is your quoted table. I wrote it for mysql but it is very simple and should work on other types of dbs as well :

select * from
(select m.*, n.num from
(select customerId, name, product, min(price) from test1
group by customerId, name, product) m
left join
    (select customerId, count(1) num from test1
group by customerId) n on m.customerId=n.customerId) sq
where ((num=2 and product='Melon') OR num=1)

NB! I'm not using window functions since there are environments where they don't work (mysql<8 etc.). Also the alphabetical order works in the example but may not do the job with the actual data.