Get minimum value row based on column values

Question

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 | |
+------------------------+---------------------+------+-----+---------+-------+

• McNets: Please add table schema.
• McNets: 2018-02-29 is not a valid date
• tombom: I don't see what's so tricky about this query. You'll need WHERE, GROUP BY (that's not clear from your question. Where's the product id in your table?), and MIN(). After completing a sql tutorial you should be able to write the appropriate query. Please have a try and post the result. It would probably clear up the question as well.
• Jaimin Sutariya: @tombom, Thanks for the suggestion, I'll try from my side and will add query to the question. PS: this is just the demo table, my original table contains a lot more data but I have only used the columns which are required.

Answer 1

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)

• Jaimin Sutariya: Thank you very much. that worked for me. I was not aware of coalesce

Answer 2

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() >= special_from_date
 and
 curdate() <= 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

• Jaimin Sutariya: Thank you for this answer, but it is not working if special_to_date is null
• McNets: I've removed rows where special_to_date is null or special_from_date is null
• Jaimin Sutariya: I mean if special_to_date for 2648 column is NULL, it should fetch the same row and not skip it. Because NULL value for special_to_date means there is no expiry date for special price.
• McNets: then you'll get two rows, 2648, 2651
• Paul Spiegel: You are grouping by the primary key. That doesn't make sence in a single table select statement.