So, here’s another generalized query to help explain how that could be achieved. In addition, it was suggested to have a sample query for cases when you might want to join this to other tables. This is so that you can tell SQL which record from the main table you want to retrieve. The sub query is then joined to the same table on the entity AND the dates. It also occurred to me that perhaps I should give a quick explanation of what is going on with the sample query: The sub query is written to get the max date for each entity (in this case Machine). When creating an aggregate (such as MAX) you don’t want to group by the value you want to find the max of. It was grouping by the date desired in the MAX aggregate. I'm kind of concerned about speed too since tables are growing kinda fast. After looking at the sub query I noticed the issue was with the GROUP BY portion of the query. MySQL JOIN two tables and get latest result Ask Question Asked 8 years ago Modified 8 years ago Viewed 3k times 2 I want to JOIN two tables and get the latest result from each one of those two in a single table. Today someone reached out to ask why they couldn’t get this to work for them in their query. The following generalized query will allow you to do this. The record you want to see is the one with the latest information (determined by the date column).You don’t want to see multiple records for a given machine meaning, you want to only return one record per machine.
The table has a date specifying the time of the record insert (or something like an inventory scan time).You have a table that can have multiple records for a given entity (such as a machine).I’ve been asked this a couple times over the past few months and again today so I thought I’d send it out in hopes that others will benefit from this. Note: there are multiple ways to do this but at the time I originally wrote this I only wrote about this one. I have left this as originally written but have updated formatting for my WordPress theme.
Mysql join latest record series#
Now I have that off my chest I can go back to using this in my real application.This was originally posted to my internal (to Microsoft) blog on as part of a series I called ‘SQL Tips’ for coworkers based on an email distribution list I sent out before the internal blog. ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 `saleID` int(10) NOT NULL auto_increment, ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 `custID` int(10) NOT NULL auto_increment, Here is the db dump if you want to play around. ON s1.custID = s2.custID AND s1.saledate s2.saledate ie even if there was no sale at all, I still want the customer data listed with NULLS for the sale data.Īnd the result set from the query below: custIDįor John and Winston we have the most recent sale they made. I want to list ALL customers together with the LAST sale they made.
I have a table of customers and any sales they may have made. I am not a MySQL expert so there may be a better way to do this. After spending a few hours searching and testing this one I thought I’d better share it since I found many similar questions but no answers.
0 kommentar(er)
