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:[email protected]]
> Sent: Monday, October 24, 2011 1:46 PM
> To: [email protected]
> 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/[email protected]
