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:[email protected]]
> Sent: Monday, October 24, 2011 4:06 PM
> To: [email protected]
> 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:[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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[email protected]
