1

I have these simple tables:

products

+----+------+---------+
| id | code | details |
+----+------+---------+
|  1 | P01  | ...     |
|  2 | P02  | ...     |
|  3 | P03  | ...     |
+----+------+---------+

prices

+----+---------------+-------------+--------+------------+------------+
| id | customerGroup | productCode | price  | date       | endDate    |
+----+---------------+-------------+--------+------------+------------+
|  1 | DEFAULT       | P01         | 1.2500 | 2018-01-01 | NULL       |
|  2 | DEFAULT       | P02         | 1.4000 | 2018-01-01 | NULL       |
|  3 | DEFAULT       | P03         | 2.0000 | 2018-01-01 | NULL       |
|  4 | DEFAULT       | P01         | 1.3000 | 2018-07-01 | NULL       |
|  5 | BLUE          | P01         | 1.3100 | 2019-01-01 | NULL       |
|  6 | BLUE          | P02         | 0.9000 | 2019-01-01 | 2019-05-01 |
|  7 | BLUE          | P03         | 2.0000 | 2019-09-01 | NULL       |
|  8 | DEFAULT       | P01         | 1.3200 | 2019-10-01 | NULL       |
|  9 | GREEN         | P01         | 0.5000 | 2019-10-01 | NULL       |
| 10 | GREEN         | P02         | 0.6000 | 2019-10-01 | NULL       |
| 11 | GREEN         | P03         | 0.7000 | 2019-10-01 | NULL       |
+----+---------------+-------------+--------+------------+------------+

Logic

  • prices is a historical table.
  • Each row represents a price record for a product and a customer group.
  • endDate = NULL means price entry is valid between date and eternity (unless conditions below occur).
  • If a price record for the same customerGroup and productCode has a newer matching date, it overrides the older ones — even if the price is higher.
  • The minimum price value for the same productCode and matching date(s) will win; as long as the customerGroup it has matches with the given group(s).

Goal 1

To get the minimum possible price record:

  • for a member of group BLUE and DEFAULT
  • for product P01
  • in date 2019-10-01
SELECT
    pp.*
FROM prices AS pp
JOIN (
    SELECT customerGroup, MAX(date) AS maxDate
    FROM prices AS pp
    WHERE productCode = 'P01'
    GROUP BY customerGroup
) AS eachRow ON (pp.customerGroup = eachRow.customerGroup AND pp.date = eachRow.maxDate)
WHERE 
    pp.productCode = 'P01'
    AND FIND_IN_SET(pp.customerGroup, 'DEFAULT,BLUE') > 0
    AND ((pp.endDate IS NULL AND '2019-10-01' >= pp.date) OR (pp.endDate IS NOT NULL AND ('2019-10-01' BETWEEN pp.date AND pp.endDate)))
GROUP BY pp.customerGroup
ORDER BY pp.price ASC
LIMIT 1;

this returns the correct / expected single result:

+----+---------------+-------------+--------+------------+------------+
| id | customerGroup | productCode | price  | date       | endDate    |
+----+---------------+-------------+--------+------------+------------+
|  5 | BLUE          | P01         | 1.3100 | 2019-01-01 | NULL       |
+----+---------------+-------------+--------+------------+------------+

Goal 2 (Question)

How to get the minimum possible price record for each product in products at once?

The result should be:

+----+---------------+-------------+--------+------------+------------+
| id | customerGroup | productCode | price  | date       | endDate    |
+----+---------------+-------------+--------+------------+------------+
|  2 | DEFAULT       | P02         | 1.4000 | 2018-01-01 | NULL       |
|  5 | BLUE          | P01         | 1.3100 | 2019-01-01 | NULL       |
|  7 | BLUE          | P03         | 2.0000 | 2019-09-01 | NULL       |
+----+---------------+-------------+--------+------------+------------+

Notes:

  • I'm looking for a way to do this without using GROUP_CONCAT in the outer query.
  • MIN + GROUP BY usage is not a solution since that won't properly return all necessary fields such as customerGroup and date...

Here is a SQL Fiddle.

  • You can replace FIND_IN_SET(pp.customerGroup, 'DEFAULT,BLUE') > 0 with pp.customerGroup IN ('DEFAULT', 'BLUE'). It's simpler, standard, and might perform better. – Schwern Nov 9 at 3:52
  • What version of MySQL are you on? Window functions added in MySQL 8 would make this a lot easier. – Schwern Nov 9 at 4:05
  • Unfortunately MySQL 5.7. – Onur Yıldırım Nov 10 at 0:39
0

Query for second question

SELECT MAX(pp.customerGroup) as CustomerGroup, MAX(pp.ProductCode) as ProductCode , MIN(pp.price) as Price FROM Prices as pp
INNER JOIN Products as pro
on pp.PRoductCode = pro.Code

GROUP BY pp.customerGroup
ORDER BY pp.customerGroup;

Correct me if I am wrong I will update the answer accordingly..

  • If this is correct then will go for your first question. – vijay sahu Nov 9 at 7:48
0

Goal 2 (Question) How to get the minimum possible price record for each product in products at once?

The answer is:

Select p.productcode, min(p.price)
from prices p
left join products prd on p.productcode = prd.code
Group by p.productcode

output: P01 0.5 P02 0.6 P03 0.7

But your request result is grouping by id, so it can not display the minimum price by product. Also the actual data in the result you show is not actually the minimum price? (which is confusing).

New contributor
Snuka is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • 1
    Thanks. But min + group by will not return all the required fields. The minimum prices in your result are wrong bec. those prices are for group GREEN. Pls read the question details in logic part. – Onur Yıldırım Nov 9 at 14:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.