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    |       |
+------------------------+---------------------+------+-----+---------+-------+
Keep in mind that if CURDATE() doesn't fit any range of dates you'll get no rows.




select   id, 
         min(price) price, 
         special_price, 
         special_from_date,
         special_to_date
from     prices
where    special_price is not null
and      special_from_date is not null
and      special_to_date is not null
and      (curdate() &gt;= special_from_date
          and
          curdate() &lt;= special_to_date)
group by id



  id |    price | special_price | special_from_date | special_to_date
---: | -------: | ------------: | :---------------- | :--------------
2648 | 299.0000 |       85.0000 | 2017-12-29        | 2018-02-28     



dbfiddle here