Unanswered: Help selecting the "date before" an event
I've been struggling with different ways to figure out a solution to my problem, and late last night when my final solution very intricately led me to right back to the unmodified data I started with, I knew I needed some help.
My sample database, for the purpose of my question, is like this:
( select max( d3.Date ) from MyTable d3
where d3.Action=1 and d3.C_ID = d1.C_ID ) as last_paid,
( select d2.Amt from MyTable d2
where d2.Action=1 and d2.C_ID = d1.C_ID and d2.Date = (
select max( d4.Date ) from MyTable d4
where d4.Action=1 and d4.C_ID = d1.C_ID ) ) as last_amt
from MyTable d1
I'm interested how you got it to work in such a small amount of SQL - does it really show the amount paid on the previous date (it looks a bit suspicious to me)?
After setting up a small test, it appears that my query does in fact not show the correct amount. It show the first amount listed.
It gives the correct Date, but only because it bypasses the sorting by using the MAX function.
I did find a few other ways to achieve the desired results, but the only way that was of any use was:
FROM `dbTest` AS `first`
INNER JOIN `dbTest` AS `second`
ON `first`.`C_ID` = `second`.`C_ID`
AND `second`.`Action` = 2
AND `first`.`Action` = 1
WHERE `first`.`O_id` = (
SELECT `third`.`O_id` FROM `dbTest` AS `third`
WHERE `third`.`C_ID` = `first`.`C_ID`
AND `third`.`Action` = 1
ORDER BY `O_id` DESC
Out of all the queries I tried, this was the only one I found that did not require a temporary table and/or filesort to function properly.
This one and the one you posted should both work fine.
Add an index to the Action column and they will both work slightly faster