d_barszczak Posted November 22, 2007 Share Posted November 22, 2007 Hi all, Just when im getting to grips with this mysql business i have another problem ??? The following query works: SELECT MAX(`order`) as `MAXorder` FROM `nav_directory`; which return MAXorder = 10. i would like it to return the tag also tag, MAXorder page1, 10 My Solution to this was: SELECT `tag`, MAX(`order`) as `MAXorder` FROM `nav_directory` GROUP BY order; But that lists all the tags. Can anyone help??? Link to comment https://forums.phpfreaks.com/topic/78399-solved-me-again-query-help-please/ Share on other sites More sharing options...
rajivgonsalves Posted November 22, 2007 Share Posted November 22, 2007 something like the following should work but not too sure SELECT `tag`, MAX(`order`) as `MAXorder` FROM `nav_directory` GROUP BY order having order=MAX(`order`) ; Link to comment https://forums.phpfreaks.com/topic/78399-solved-me-again-query-help-please/#findComment-396755 Share on other sites More sharing options...
d_barszczak Posted November 22, 2007 Author Share Posted November 22, 2007 Thanks but that gives me the same result. I want the highest value of `order` and the `tag` that accompanies it. Thanks. Link to comment https://forums.phpfreaks.com/topic/78399-solved-me-again-query-help-please/#findComment-396767 Share on other sites More sharing options...
fenway Posted November 22, 2007 Share Posted November 22, 2007 The robust way to do this is as follows (untested): SELECT n.tag, n.order FROM nav_directory AS n JOIN ( SELECT MAX(order) as MAXorder FROM nav_directory ) AS sub ON ( sub.MAXorder = n.order ) Of course, you could add as many conditions as you wanted. Don't forget about the boundary case; you may want an order by, too. Link to comment https://forums.phpfreaks.com/topic/78399-solved-me-again-query-help-please/#findComment-396768 Share on other sites More sharing options...
d_barszczak Posted November 22, 2007 Author Share Posted November 22, 2007 I used this in the end. SELECT `order`, `tag` FROM `nav_directory` ORDER BY `order` desc LIMIT 1; This alway returns the highest value with the tag. EDIT: Also gone and named `order` which is a reserved word. Need to be more careful with my naming stratergy. Link to comment https://forums.phpfreaks.com/topic/78399-solved-me-again-query-help-please/#findComment-396776 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.