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=arch...@jab.org
