AnalyzeThis Posted January 16, 2013 Share Posted January 16, 2013 Here is the database (basically ) =================== | name | type | timestamp ==================== | Brain | Blue Car | 1/2/2012 | Brain | Blue Car | 2/2/2012 | Brain | Red Car | 3/2/2013 | Fred | Blue Car | 2/2/2012 | Fred | Red Car | 2/2/2012 | Fred | Red Car | 1/2/2010 | Brenda | Red Car | 2/2/2012 | Wendy | Red Car | 1/2/2013 I want to return a list where only the distinct name and type is returned with only the most recent timestamp e.g. =================== | name | type | timestamp ==================== | Brain | Blue Car | 2/2/2012 | Brain | Red Car | 3/2/2013 | Fred | Blue Car | 2/2/2012 | Fred | Red Car | 2/2/2012 | Brenda | Red Car | 2/2/2012 | Wendy | Red Car | 1/2/2013 I do not want to delete any old records or create a new table. I just want to run a query within php to obtain this. I'm a little brain dead at the moment and googling isn't providing much use. Thank you! Quote Link to comment https://forums.phpfreaks.com/topic/273245-query-only-most-recent-timestamp-based-on-two-other-distinct-columns/ Share on other sites More sharing options...
AnalyzeThis Posted January 16, 2013 Author Share Posted January 16, 2013 I'm thinking it maybe something like: Select name, type, max(timestamp) GROUP BY name, type Quote Link to comment https://forums.phpfreaks.com/topic/273245-query-only-most-recent-timestamp-based-on-two-other-distinct-columns/#findComment-1406172 Share on other sites More sharing options...
stijn0713 Posted January 16, 2013 Share Posted January 16, 2013 if you think that, why not do it ? i don't even think you need max() Quote Link to comment https://forums.phpfreaks.com/topic/273245-query-only-most-recent-timestamp-based-on-two-other-distinct-columns/#findComment-1406182 Share on other sites More sharing options...
AnalyzeThis Posted January 16, 2013 Author Share Posted January 16, 2013 I wanted to make certain this seemed to of work: $DB02->Query("SELECT `a`,`b`.`ID` AS 'c' , `d` , `e` , `f` , DATE_FORMAT( max(`g`), '%m/%d/%Y' ) AS `date` FROM `h` LEFT JOIN `i` ON `a`.`b` = `x`.`ID` WHERE `a` = '{$x["ID"]}' AND y = 1 GROUP BY `a`, `b` ORDER BY `c` ASC LIMIT {$set_limit}, {$limit}"); Quote Link to comment https://forums.phpfreaks.com/topic/273245-query-only-most-recent-timestamp-based-on-two-other-distinct-columns/#findComment-1406200 Share on other sites More sharing options...
Barand Posted January 16, 2013 Share Posted January 16, 2013 $DB02->Query("SELECT `a`,`b`.`ID` AS 'c' , `d` , `e` , `f` , DATE_FORMAT( max(`g`), '%m/%d/%Y' ) AS `date` FROM `h` LEFT JOIN `i` ON `a`.`b` = `x`.`ID` WHERE `a` = '{$x["ID"]}' AND y = 1 GROUP BY `a`, `b` ORDER BY `c` ASC LIMIT {$set_limit}, {$limit}"); What has that query got to do with your original post with a single table with three columns? Quote Link to comment https://forums.phpfreaks.com/topic/273245-query-only-most-recent-timestamp-based-on-two-other-distinct-columns/#findComment-1406249 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.