Q:

Get minimum value row based on column values

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.

Check price column for minimum product price. 
Skip special_price if NULL
Skip special_price
if special_from_date and special_to_date is NULL or current
date does not fall between range 
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    |       |
+------------------------+---------------------+------+-----+---------+-------+

A:

Get the current price as follows:
Use special_price when:

special_price is not NULL
special_from_date is not NULL
special_from_date is not greater than today
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)

