MySQL – left join on last (or first) record in the right table

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. I am not a MySQL expert so there may be a better way to do this. Please tell me if there is!!

This is a simplified example. I have a table of customers and any sales they may have made. I want to list ALL customers together with the LAST sale they made. ie even if there was no sale at all, I still want the customer data listed with NULLS for the sale data.

Sample Customer data:

custID custFirst custLast
1 John Smith
2 Sally Fields
3 Winston Churchill

Sample Sales data:

saleID saleDate saleAmount custID
1 2008-10-20 09:12:00 20 1
2 2007-12-03 18:45:00 14 1
3 2008-02-13 16:00:00 10 3
4 2006-07-08 18:00:00 30 3
5 2007-05-14 14:48:00 18 3
6 2008-10-22 19:00:00 57 1

And the result set from the query below:

custID custFirst custLast saleID saleDate saleAmount custID
1 John Smith 6 2008-10-22 19:00:00 57 1
2 Sally Fields (NULL) (NULL) (NULL) (NULL)
3 Winston Churchill 3 2008-02-13 16:00:00 10 3

For John and Winston we have the most recent sale they made. Sally hasnt purchased anything yet.

This is the SQL:

SELECT
    customers.*, sale_tmp.*
FROM
    customers

    LEFT JOIN ( SELECT s1.*
		FROM sales as s1
		LEFT JOIN sales AS s2
		     ON s1.custID = s2.custID AND s1.saledate < s2.saledate
		WHERE s2.custID IS NULL ) as sale_tmp

    ON (customers.custID = sale_tmp.custID)

This relies on a couple of sql concepts linked together. The first is the idea that you can LEFT JOIN a temporary table just like a “real” table. So, this bit:

( SELECT s1.*
		FROM sales as s1
		LEFT JOIN sales AS s2
		     ON s1.custID = s2.custID AND s1.saledate < s2.saledate
		WHERE s2.custID IS NULL ) as sale_tmp

creates a temporary table called “sale_tmp” that we are LEFT JOINing to the customers table via the common custID columns. Dont worry about the table construction, just note that a table is being created here.

The second concept is called a within-group aggregate and this is the way that we get the most recent sale for each custID in the sales table. There is a very good explanation of that here:

http://www.artfulsoftware.com/infotree/queries.php?&bw=1395#101

So, to sum up, the technique is to create a temporary table (sale_tmp) that contains just the most recent sales for each custID in the sales table, then left join sale_tmp to customers.

If you wanted their first purchases, simply change

s1.saledate < s2.saledate

to

s1.saledate > s2.saledate

Here is the db dump if you want to play around.


CREATE TABLE `customers` (
  `custID` int(10) NOT NULL auto_increment,
  `custFirst` varchar(50) default NULL,
  `custLast` varchar(50) default NULL,
  PRIMARY KEY  (`custID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `customers` */

insert  into `customers`(`custID`,`custFirst`,`custLast`) values (1,'John','Smith'),(2,'Sally','Fields'),(3,'Winston','Churchill');

/*Table structure for table `sales` */

CREATE TABLE `sales` (
  `saleID` int(10) NOT NULL auto_increment,
  `saleDate` datetime default NULL,
  `saleAmount` double default NULL,
  `custID` int(10) default '0',
  PRIMARY KEY  (`saleID`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

/*Data for the table `sales` */

insert  into `sales`(`saleID`,`saleDate`,`saleAmount`,`custID`) values (1,'2008-10-20 09:12:00',20,1),(2,'2007-12-03 18:45:00',14,1),(3,'2008-02-13 16:00:00',10,3),(4,'2006-07-08 18:00:00',30,3),(5,'2007-05-14 14:48:00',18,3),(6,'2008-10-22 19:00:00',57,1);

Now I have that off my chest I can go back to using this in my real application.

About these ads

30 thoughts on “MySQL – left join on last (or first) record in the right table

  1. Works great unless there is more than one sales record for the date of the latest sale. If there is more than one sale on that day you get a duplicate records for each sale. The following change to the code fixes this. In my case sale prices would be the same so grabbing any of them was fine for me. The following would not work if you wanted other details like the biggest or smallest sale of the day.

    This code:

    ON s1.custID = s2.custID AND s1.saledate < s2.saledate
    WHERE s2.custID IS NULL

    Should be changed to this:

    ON s1.custID = s2.custID AND s1.saledate < s2.saledate
    WHERE s2.custID IS NULL limit 1

    Maybe it will help the next person. Regardless I learned something new today.

    R.Dude

    • I think the LIMIT statement in subquery will not depend on the main query. So result of
      ( SELECT s1.*
      FROM sales as s1
      LEFT JOIN sales AS s2
      ON s1.custID = s2.custID AND s1.saledate < s2.saledate
      WHERE s2.custID IS NULL LIMIT 1)

      will allways be the same record for each row of main query. So all fields selected from joined table sale_tmp will be NULL except for the one where customers.custID will be equal to custID in that particular first row.

      Please correct me if I’m wrong. (And by wrong I don’t mean my bad English)

      • Your right, that adjustment would only work if you wanted a single persons latest sale and they happened to have multiple on the same day. It doesn’t produce a list of everyones latest because at you stated they all return NULL except one one that falls in the LIMIT.

  2. You are my hero. This is a much more elegant solution that doing sub-queries in the SELECT or trying to do it with a regular left join and grouping. The latter works, but it doesn’t allow you to sort the records in the table you are joining on (the temp table).

    Thanks so much!!

  3. Excellent, thanks! Did exactly what I needed it to. Never would have figured this out on my own. It’s just sad that there isn’t a more elegant way to do this.

    • Thanks. Yes, it is a bit convoluted!

      As a tip I have found that keeping the inner SELECT as small as possible made a big difference to the speed of the overall query – which is obvious I suppose. I had another application of this where that inner SELECT returned only a few records at first but became very large (>1000 records) over time and adding a few more WHERE clauses to the inner SELECT to trim the number down made a huge difference.

  4. Thanks a lot, I was looking for this along time ago (… not clear why on artful site was not found earlier… my fault :P ); always my solutions was to build the temporary table as a static object (or on memory if was a procedure related job… but ussually I use this kind of stuff for upload / update specific data); with this I´m able to save time doing complex queries without build intermediate static tables

  5. I tired the limit 1 addition since I have a table that I am matching that has several entires from the same day.
    It ends up having issues though when I add the “limit 1″.
    Turns the rest of the info to NULL.

  6. For some magic reason, adding a GROUP BY clause to a JOIN, gives you the first record of the second table:

    SELECT customers.*, sales.*
    FROM customers
    LEFT JOIN sales ON (customers.custID = sales.custID)
    GROUP BY customers.custID

    I do not know if this query can be changed to suit your needs…

  7. The GROUP BY clause only works if you have the latest purchases first in the database table for each customer, because the GROUP BY picks first rows for data and discards all the rest per each condition.

    Best solution I’ve come up with is to have a subquery to order up the purchases in DESCENDING order, another subquery to GROUP them for each customer and then join them to the customers list.
    It would look like this:

    SELECT
    c.*, s.*
    FROM
    customers AS c
    LEFT JOIN (
    SELECT *
    FROM (
    SELECT *
    FROM sales
    ORDER BY saledate DESC
    ) GROUP BY custID
    ) AS s
    ON (c.custID = s.custID)

  8. Good idea, just a small rationalization to to the original query from me as well (in order to keep the inner query simpler):

    SELECT
    customers.*, sale_tmp.*
    FROM
    customers
    LEFT JOIN (
    SELECT s.* FROM sales as s ORDER BY saleDate DESC
    ) as sale_tmp ON customers.custID = sale_tmp.custID

    GROUP BY customers.custID

    Seems more clear this way, but I admit that I haven’t put them to the test to see which one would be more efficient,

    P.S. I am no SQL guru either ;)

  9. I have similiar problem. But I need the last record to some date. If I look on your data, let’s say I need the last sale of each user till ’2008-10-21′. So for the record of John Smith it will select sale from ’2008-10-20′, not from ’2008-10-22′. Any suggestions?

  10. Thank you for your great solution! That’s exactly what I was looking for!
    My problem was very similar: 3 tables (users, accounts and orders) and I used to merge two massive arrays in php in order to have a single array.
    It produced even more problems when I needed to sort, filter and paginate data, because it required “walking” through the whole array unsetting some of it, so it required loads of memory and CPU.
    Now everything is sorted, filtered and paginated on DB side!! That’s amazing!

  11. Great solution – thank you. Previously I’ve just pulled in the whole lot and filtered out what I don’t need in software. Clearly, doing it all in SQL is far, far better. It takes a bit of documenting in the code though, as it’s not immediately obvious what it’s doing. It still rocks though :-)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s