List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:October 24 2011 8:46pm
Subject:Within-group aggregate query help please - customers and latest subscription row
View as plain text  
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

Thread
Within-group aggregate query help please - customers and latest subscription rowDaevid Vincent24 Oct
  • RE: Within-group aggregate query help please - customers and latest subscription rowDaevid Vincent25 Oct
    • RE: Within-group aggregate query help please - customers and latest subscription rowDaevid Vincent25 Oct
      • RE: Within-group aggregate query help please - customers andlatest subscription rowhsv25 Oct