kevinfwb Posted October 22, 2007 Share Posted October 22, 2007 I have a database that we use to log communication with our stores. I have a summary page that (I want) to show the most recent communication for each store number (storeNum). I'm pretty sure that I need to use the aggregate function, MAX, on the date field and/or possibly a subquery but I just can't seem to figure this one out. Here is the basic query that returns all communications for all dates for all stores. I want it to just return the most recent communication for each store. SELECT communications.storeNum as storeNum , communications.method as method, communications.date as date, communications.subject as subject, communications.details as details, Stores.franchisee as franchisee, Stores.rfc as rfc FROM communications INNER JOIN Stores ON communications.storeNum = Stores.storeNum ORDER BY communications.date DESC Any help is greatly appreciated -Kevin Link to comment https://forums.phpfreaks.com/topic/74373-solved-querying-the-max-row/ Share on other sites More sharing options...
fenway Posted October 22, 2007 Share Posted October 22, 2007 You're right, a second query would be best... use MAX(datetimeField) on the comm table, and get back the date/time and storeNum... then join this derived table to the stores table. Link to comment https://forums.phpfreaks.com/topic/74373-solved-querying-the-max-row/#findComment-375755 Share on other sites More sharing options...
kevinfwb Posted October 22, 2007 Author Share Posted October 22, 2007 I don't think that I've ever done that before, would this still be a single query with a subquery or 2 separate queries all together? I'd like it to be a single query since I'm using it to populate an HTML table at the same time. Link to comment https://forums.phpfreaks.com/topic/74373-solved-querying-the-max-row/#findComment-375761 Share on other sites More sharing options...
fenway Posted October 23, 2007 Share Posted October 23, 2007 I don't think that I've ever done that before, would this still be a single query with a subquery or 2 separate queries all together? I'd like it to be a single query since I'm using it to populate an HTML table at the same time. As far the PHP side is concerned it doesn't matter... it's 2 "queries", but one statement... something like this (untested) -- you may need to backtick the date column (reserved keyword!): SELECT c.storeNum, c.method c.date, c.subject as subject, c.details s.franchisee s.rfc FROM communications AS c INNER JOIN stores AS s ON c.storeNum = s.storeNum INNER JOIN ( SELECT storeNum, MAX(date) AS maxDate FROM communications GROUP BY storeNum ) AS rc ON rc.storeNum = c.storeNum and rc.maxDate = c.date ORDER BY c.date DESC Link to comment https://forums.phpfreaks.com/topic/74373-solved-querying-the-max-row/#findComment-376204 Share on other sites More sharing options...
kevinfwb Posted October 24, 2007 Author Share Posted October 24, 2007 This worked perfectly - thanks for your help! -Kevin Link to comment https://forums.phpfreaks.com/topic/74373-solved-querying-the-max-row/#findComment-376988 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.