Getting the Correct Max Date

deanlogic (61)in #php • 6 years ago (edited)

After I added the dynamic search feature to my “To The Moon” 🚀 Coin Calculator, I noticed that the price of BitCoin (BTC) was not increasing. I knew that it was over USD$9,000 all day, but it was still showing a price just about USD$8,000. When I ran the query in the MySQL database, I noticed that it was pulling back the correct date, but not the correct price.

QUERY FAIL!!

So, it was off to figure out why my query wasn’t working.

To start, I was using the MAX() function to retrieve the latest date added to the table. This should have brought back the latest entry for each coin, because I was also grouping the coins by ID. Well, for some reason, that was just wasn’t matching up and bringing back different data for the same coin.

Time to do some searching…

After attempting and failing with a few search results, I found W3Resource and their page on SQL MAX() on date value. What a great page. It goes through progressively complex uses of MAX() on date until it got to where I needed to be, which also happened to be their last example.

SQL max() on date value using join

Part of my issues was that I needed to join the coin table with the market_history table and I think that was throwing off my original query. Also, I really wasn’t using the MAX() function correctly.

The first step is to get the columns I needed from my Main table. Simple enough.

SELECT mh.`ID`, mh.`RowAdded`, mh.`Rank`, mh.`Price_usd`, mh.`Price_btc`, mh.`SupplyAvailable`, mh.`SupplyTotal`, mh.`SupplyMax`
FROM `market_history` mh

Then I needed to pull the latest date value in the table. Again, simple use of the MAX() function.

SELECT MAX(`RowAdded`) FROM `market_history`

Now, I needed to make a connection from my Main table and the query limiting the date to the MAX() value. That is done by setting my Main table RowAdded column equal to the RowAdded column of the limiting query.

SELECT mh.`ID`, mh.`RowAdded`, mh.`Rank`, mh.`Price_usd`, mh.`Price_btc`, mh.`SupplyAvailable`, mh.`SupplyTotal`, mh.`SupplyMax`
FROM `market_history` mh
WHERE mh.`RowAdded` = (SELECT MAX(`RowAdded`) FROM `market_history` WHERE `ID` = mh.`ID`)

In order to get the coins in the proper order, I need to sort them by rank. This is done by setting the Order By in the Main table.

SELECT mh.`ID`, mh.`RowAdded`, mh.`Rank`, mh.`Price_usd`, mh.`Price_btc`, mh.`SupplyAvailable`, mh.`SupplyTotal`, mh.`SupplyMax`
FROM `market_history` mh
WHERE mh.`RowAdded` = (SELECT MAX(`RowAdded`) FROM `market_history` WHERE `ID` = mh.`ID`) ORDER BY mh.`Rank`

Since I am pulling 500 coin records each cron job, I want to limit the default display to just the top 100, so I also add a Limit at this point, which affects the Main table.

SELECT mh.`ID`, mh.`RowAdded`, mh.`Rank`, mh.`Price_usd`, mh.`Price_btc`, mh.`SupplyAvailable`, mh.`SupplyTotal`, mh.`SupplyMax`
FROM `market_history` mh
WHERE mh.`RowAdded` = (SELECT MAX(`RowAdded`) FROM `market_history` WHERE `ID` = mh.`ID`) ORDER BY mh.`Rank` LIMIT 100

Now, I need to get extra information from the coin table, the coin name and symbol. To get these two tables together, without messing up the order and data from the Main table, I JOIN the secondary table to the first one by a unique column, which in this case is the ID column

c.`Name`, c.`Symbol`
LEFT JOIN `coin` c 
ON mh.`ID` = c.`ID`

Now, I just add that together to create the full query.

SELECT mh.`ID`, mh.`RowAdded`, mh.`Rank`, mh.`Price_usd`, mh.`Price_btc`, mh.`SupplyAvailable`, mh.`SupplyTotal`, mh.`SupplyMax`, c.`Name`, c.`Symbol`
FROM `market_history` mh
LEFT JOIN `coin` c 
ON mh.`ID` = c.`ID`
WHERE mh.`RowAdded` = (SELECT MAX(`RowAdded`) FROM `market_history` WHERE `ID` = mh.`ID`) ORDER BY mh.`Rank` LIMIT 100

I use basically the same query when the user searches for a specific coin, only I don’t need to user the ORDER BY or LIMIT options, but I need to make sure that the search parameter limits the query to just the one coin. I can use the coin table to limit the rows to search item.

SELECT mh.`ID`, mh.`RowAdded`, mh.`Rank`, mh.`Price_usd`, mh.`Price_btc`, mh.`SupplyAvailable`, mh.`SupplyTotal`, mh.`SupplyMax`, c.`Name`, c.`Symbol`
FROM `market_history` mh
LEFT JOIN `coin` c 
ON mh.`ID` = c.`ID`
WHERE mh.`RowAdded` = (SELECT MAX(`RowAdded`) FROM `market_history` WHERE `ID` = mh.`ID`) 
AND c.ID = '" . $searchCoin . "'