Okay, it seems I am learning... slowly... So there needs to be a second WHERE in the sub-select...
To get ONE customer's last subscription (0.038s): SELECT c.customer_id, c.email, c.name, c.username, s.subscription_id, s.`date` FROM customers AS c INNER JOIN customers_subscriptions AS s ON c.customer_id = s.customer_id INNER JOIN (SELECT MAX(`date`) AS LastDate, customer_id FROM customers_subscriptions AS cs WHERE cs.customer_id = 7 GROUP BY customer_id ) AS `x` ON s.customer_id = x.customer_id AND s.date = x.LastDate WHERE c.customer_id = 7; To get ALL customers and their last subscription row (1m:28s) SELECT c.customer_id, c.email, c.name, c.username, s.subscription_id, s.`date` FROM customers AS c INNER JOIN customers_subscriptions AS s ON c.customer_id = s.customer_id INNER JOIN (SELECT MAX(`date`) AS LastDate, customer_id FROM customers_subscriptions AS cs GROUP BY customer_id ) AS `x` ON s.customer_id = x.customer_id AND s.date = x.LastDate ORDER BY customer_id LIMIT 10; Thanks to "you know who you are" for pointing me in the right direction. Hopefully this helps someone else. d. > -----Original Message----- > From: Daevid Vincent [mailto:dae...@daevid.com] > Sent: Monday, October 24, 2011 4:06 PM > To: mysql@lists.mysql.com > Subject: RE: Within-group aggregate query help please - customers and latest > subscription row > > A kind (and shy) soul replied to me off list and suggested this solution, > however, > this takes 28 seconds (that's for a single customer_id, so this is not going > to scale). > Got any other suggestions? :-) > > SELECT > c.customer_id, > c.email, > c.name, > c.username, > s.subscription_id, > s.`date` > FROM > customers AS c > INNER JOIN customers_subscriptions AS s > ON c.customer_id = s.customer_id > INNER JOIN > (SELECT > MAX(`date`) AS LastDate, > customer_id > FROM > customers_subscriptions AS cs > GROUP BY customer_id) AS `x` > ON s.customer_id = x.customer_id > AND s.date = x.LastDate > WHERE c.customer_id = 7; > > There are 781,270 customers (nearly 1 million) and 1,018,092 > customer_subscriptions. > > Our tables have many indexes on pretty much every column and for sure the > ones we use here. > > EXPLAIN says: > > id select_type table type possible_keys key > key_len ref rows Extra > ------ ----------- ---------- ------ ---------------- ----------- > ------- ------ ------- ------------------------------- > 1 PRIMARY c const PRIMARY PRIMARY 4 > const 1 > 1 PRIMARY s ref date,customer_id customer_id 4 > const 2 > 1 PRIMARY <derived2> ALL (NULL) (NULL) > (NULL) (NULL) 781265 Using where > 2 DERIVED cs ALL (NULL) (NULL) > (NULL) (NULL) 1018092 Using temporary; Using filesort > > > -----Original Message----- > > From: Daevid Vincent [mailto:dae...@daevid.com] > > Sent: Monday, October 24, 2011 1:46 PM > > To: mysql@lists.mysql.com > > Subject: Within-group aggregate query help please - customers and latest > > subscription row > > > > I know this is a common problem, and I've been struggling with it for a > full > > day now but I can't get it. > > > > I also tried a few sites for examples: > > http://www.artfulsoftware.com/infotree/queries.php#101 > > > http://forums.devarticles.com/general-sql-development-47/select-max-datetime > > -problem-10210.html > > > > Anyways, pretty standard situation: > > > > CREATE TABLE `customers` ( > > `customer_id` int(10) unsigned NOT NULL auto_increment, > > `email` varchar(64) NOT NULL default '', > > `name` varchar(128) NOT NULL default '', > > `username` varchar(32) NOT NULL, > > ... > > ); > > > > CREATE TABLE `customers_subscriptions` ( > > `subscription_id` bigint(12) unsigned NOT NULL default '0', > > `customer_id` int(10) unsigned NOT NULL default '0', > > `date` date NOT NULL default '0000-00-00', > > ... > > ); > > > > I want to show a table where I list out the ID, email, username, and LAST > > SUBSCRIPTION. > > > > I need this data in TWO ways: > > > > The FIRST way, is with a query JOINing the two tables so that I can easily > > display that HTML table mentioned. That is ALL customers and the latest > > subscription they have. > > > > The SECOND way is when I drill into the customer, I already know the > > customer_id and so don't need to JOIN with that table, I just want to get > > the proper row from the customers_subscriptions table itself. > > > > SELECT * FROM `customers_subscriptions` WHERE customer_id = 7 ORDER BY > > `date` DESC; > > > > subscription_id processor customer_id date > > --------------- --------- ----------- ---------- > > 134126370 chargem 7 2005-08-04 > > 1035167192 billme 7 2004-02-08 > > > > SELECT MAX(`date`) FROM `customers_subscriptions` WHERE customer_id = 7 > > GROUP BY customer_id; > > > > gives me 2005-08-04 obviously, but as you all know, mySQL completely takes > a > > crap on your face when you try what would seem to be the right query: > > > > SELECT subscription_id, MAX(`date`) FROM `customers_subscriptions` WHERE > > customer_id = 7 GROUP BY customer_id; > > > > subscription_id MAX(`date`) > > --------------- ----------- > > 1035167192 2005-08-04 > > > > Notice how I have the correct DATE, but the wrong subscription_id. > > > > In the example web sites above, they seem to deal more with finding the > > MAX(subscription_id), which in my case will not work. > > > > I need the max DATE and the corresponding row (with matching > > subscription_id). > > > > Thanks, > > > > d > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org