CODEWITHSUNDEEP
mysqlsqlqsqlquery
Bruno
Bruno

Reputation: 53

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

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?

Upvotes: 1

Views: 124

Answers (2)

Alex
Alex

Reputation: 17289

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()

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270513

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.

Upvotes: 1

Related Questions