Find the row with today's valid price depending on a validFrom date in mySQL

Question

Here it comes. I have the following data:

type, validfrom, price
1, 2018-01-15, 10 
1, 2018-01-20, 20 
1, 2018-01-25, 30 
2, 2018-01-01, 12 
3, 2018-01-01, 18

Today, it's 2018-01-21, I need a query which produces the following result:

1, 2018-01-20, 20
2, 2018-01-01, 12
3, 2018-01-01, 18

I tried different combinations using sub-selects and group-bys but I wasn't able to find a nice, short query solving this problem (and that's working as well).

Any ideas?

score 1 · Accepted Answer · edited Jan 21 '18 at 14:35

1

In MySQL, you can do:

select t.*
from t
where t.validfrom = (select max(t2.validfrom)
                     from t t2
                     where t2.type = t.type and
                           t2.validfrom < now()
                    );

With an index on t(type, validfrom), this should have very good performance.

edited Jan 21 '18 at 14:35

Tim Biegeleisen

502,043
27
286
360

answered Jan 21 '18 at 14:31

Gordon Linoff

1,242,037
58
646
786

This is the first of many SO answers related to my problem I see proposing to use a **sub query within the WHERE clause**. The query chosen here seems to me quite obvious and easy to understand; and that is the reason I will accept it as the answer. Thank you. – Bruno Jan 21 '18 at 14:46

Alex · Answer 2 · 2018-01-21T15:05:33.917

0

http://sqlfiddle.com/#!9/47c9e5/8

SELECT p.*
FROM prices p
LEFT JOIN prices p_
ON p.type = p_.type
   AND p.validfrom < p_.validfrom
   AND p_.validfrom< NOW()
WHERE p_.type IS NULL
    AND p.validfrom<=NOW()

edited Jan 21 '18 at 15:05

answered Jan 21 '18 at 14:45

Alex

16,739
1
28
51

Thank you Alex, for sharing. Your query combines the actual validFrom with one closer to today, but still before today. If there is a value in p_, it might not have been the last entry before today. This query works quite well with a detour using the NULL value selection. Gordons solution with the sub select within the WHERE might be the easier solution to understand, at least for me. Thanks anyway. – Bruno Jan 21 '18 at 15:38
I feel like mine one will be faster on bigger data set – Alex Jan 21 '18 at 15:54
Yes, I would agree on that. – Bruno Jan 21 '18 at 16:42
This solution is known to perform poorly on a bigger group size. It might be fine for an average group size <= 10. If the group size grows by a factor 10, the execution time grows by the factor 100 (10²) because of the self join. While the execution time of Gordons query is linear to the group size (given that MySQL is able to cache the subquery result and execute it only once per group). – Paul Spiegel Jan 21 '18 at 21:56
@PaulSpiegel Thank you very much. Can you please share any SO post to read about this? – Alex Jan 21 '18 at 21:57
@Alex Maybe [this one](https://stackoverflow.com/a/8757062/5563083). It also contains (IMHO) the best solution for the "Top row per group" problem (at least for a bigger group size) which is similar to [this one](https://stackoverflow.com/a/7745635/5563083). – Paul Spiegel Jan 21 '18 at 22:08
@PaulSpiegel thank you. that is something I should to know for future :-) – Alex Jan 21 '18 at 22:29

Find the row with today's valid price depending on a validFrom date in mySQL

2 Answers2