Mysql – Get minimum value row based on column values

MySQL

I have below table which stores my product prices and special prices data for my products. I want to get the least price product from below data.

+------+-------+---------------+-------------------+-----------------+
|  id  | price | special_price | special_from_date | special_to_date |
+------+-------+---------------+-------------------+-----------------+
| 2647 |   299 |           251 | NULL              | NULL            |
| 2648 |   299 |            85 | 2017-12-29        | 2018-02-28      |
| 2649 |   299 |          NULL | 2017-12-29        | 2018-02-28      |
| 2650 |   299 |            55 | 2017-12-29        | 2018-01-01      |
| 2651 |   299 |           179 | 2017-12-29        | NULL            |
+------+-------+---------------+-------------------+-----------------+

The tricky part here is that I want to check below conditions.

  1. Check price column for minimum product price.
  2. Skip special_price if NULL
  3. Skip special_price
    if special_from_date and special_to_date is NULL or current
    date does not fall between range
  4. Check special_price column if
    special_from_date is not NULL and special_from_date is less than current date and special_to_date is either
    NULL or greater than current date.

The output will be like below.

+------+-------+---------------+-------------------+-----------------+
|  id  | price | special_price | special_from_date | special_to_date |
+------+-------+---------------+-------------------+-----------------+
| 2648 |   299 |            85 | 2017-12-29        | 2018-02-28      |
+------+-------+---------------+-------------------+-----------------+

Here is my table schema.

+------------------------+---------------------+------+-----+---------+-------+
| Field                  | Type                | Null | Key | Default | Extra |
+------------------------+---------------------+------+-----+---------+-------+
| id                     | int(10) unsigned    | NO   | PRI | NULL    |       |
| price                  | decimal(12,4)       | YES  |     | NULL    |       |
| special_price          | decimal(12,4)       | YES  |     | NULL    |       |
| special_from_date      | date                | YES  |     | NULL    |       |
| special_to_date        | date                | YES  |     | NULL    |       |
+------------------------+---------------------+------+-----+---------+-------+

Best Answer

Get the current price as follows:

Use special_price when:

  1. special_price is not NULL
  2. special_from_date is not NULL
  3. special_from_date is not greater than today
  4. One of the following applies (OR condition)
    • special_to_date is NULL (open)
    • special_to_date is not less than current date

Otherwise use (default) price.

Since a comparison will "fail" if one side is NULL, (2) is redundant with (3) and can be skipped.

To avoid an OR-condition, (4) can be expressed as coalesce(special_to_date, curdate()) >= curdate().

select p.*,
  case
    when  special_price is not null -- 1
      and curdate() >= special_from_date -- 2 and 3
      and curdate() <= coalesce(special_to_date, curdate()) -- 4
    then special_price
    else price
  end as current_price
from prices p
order by current_price asc
limit 1

Demo: http://rextester.com/ETEU48971

Note that it's not quite clear, if you want to include special_from_date and special_to_date into the range. In this case they are included. If you want to exclude them, you can use:

      and curdate() > special_from_date
      and curdate() < coalesce(special_to_date, curdate() + interval 1 day)
Related Question