jj20051 Posted August 25, 2010 Share Posted August 25, 2010 I need mysql to select the highest id in a table, select the first 7 rows and flip the results so that the highest id is the last one displayed. (This is a while() function). Example Table: id, pageviews 1, 100 2, 200 3, 300 4, 400 5, 500 6, 600 7, 700 8, 800 9, 900 The query would pull out: 3, 300 4, 400 5, 500 6, 600 7, 700 8, 800 9, 900 Unfortunatly I don't know how to do this and some help would be appreciated. I hope I've explained it well enough for someone to understand what I'm trying to do. Its for a graph and I want the most recent data to be on the right side instead of the left which is what you'll get if you run a query like: SELECT * FROM traffic ORDER BY id DESC LIMIT 7 Link to comment https://forums.phpfreaks.com/topic/211676-limit-and-order-by/ Share on other sites More sharing options...
kickstart Posted August 25, 2010 Share Posted August 25, 2010 Hi Afraid the only thing I can suggest is SELECT * FROM (SELECT * FROM traffic ORDER BY id DESC LIMIT 7 ) a ORDER BY id All the best Keith Link to comment https://forums.phpfreaks.com/topic/211676-limit-and-order-by/#findComment-1103477 Share on other sites More sharing options...
Mchl Posted August 25, 2010 Share Posted August 25, 2010 Hi Afraid the only thing I can suggest is Why afraid? Perfectly valid solution. Link to comment https://forums.phpfreaks.com/topic/211676-limit-and-order-by/#findComment-1103484 Share on other sites More sharing options...
kickstart Posted August 25, 2010 Share Posted August 25, 2010 Why afraid? Perfectly valid solution. Valid but I am not keen on using subselect except when necessary, and having over half the whole select being a small subselect just doesn't feel right to me. All the best Keith Link to comment https://forums.phpfreaks.com/topic/211676-limit-and-order-by/#findComment-1103493 Share on other sites More sharing options...
jj20051 Posted August 25, 2010 Author Share Posted August 25, 2010 It works perfectly as far as I can tell... Is there something that is wrong with using a subselect or is it a design flaw on my part database wise? Link to comment https://forums.phpfreaks.com/topic/211676-limit-and-order-by/#findComment-1103496 Share on other sites More sharing options...
kickstart Posted August 25, 2010 Share Posted August 25, 2010 Hi Subselects work fine, and performance wise are fine most of the time but they can struggle if the database cannot pass an index up to the outer select. All the best Keith Link to comment https://forums.phpfreaks.com/topic/211676-limit-and-order-by/#findComment-1103498 Share on other sites More sharing options...
Mchl Posted August 26, 2010 Share Posted August 26, 2010 You wouldn't need index here anyway. Just 7 rows to sort. Link to comment https://forums.phpfreaks.com/topic/211676-limit-and-order-by/#findComment-1104052 Share on other sites More sharing options...
kickstart Posted August 26, 2010 Share Posted August 26, 2010 You wouldn't need index here anyway. Just 7 rows to sort. Mmmm, but never trust a system not to grow dramatically or people to change what they want (ie, how many rows required). All the best Keith Link to comment https://forums.phpfreaks.com/topic/211676-limit-and-order-by/#findComment-1104084 Share on other sites More sharing options...
jj20051 Posted September 13, 2010 Author Share Posted September 13, 2010 This is a traffic log, while it may only need to pull 7 rows out at a time, imagine after a few weeks worth of traffic data how useful pulling only the latest data and putting it into a graph would be Link to comment https://forums.phpfreaks.com/topic/211676-limit-and-order-by/#findComment-1110451 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.