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! 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 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() 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}"); 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 Quote $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? 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
Archived
This topic is now archived and is closed to further replies.