Troy2000 Posted July 16, 2015 Share Posted July 16, 2015 I have been looking for an answer to a problem with sorting all day and cannot seem to find the correct answer. The following is my sql query: SELECT Id, zone, xtime, xdate, temp, status FROM temp_log WHERE zone LIKE 'Unit%' ORDER BY Id DESC LIMIT 3 I know that the last 3 entries will contain the 3 units and I always get the last 3 entries in the table, however, I cannot seem to get the order correct. : sometimes: Unit03 Unit01 Unit02 or: Unit01 Unit03 Unit02 sometimes it is the way I want it: Unit01 Unit02 Unit03 What do I need to add to the query to get the zone names in the correct order? Or possibly a better way of doing what I want to do. Thanks, Troy Quote Link to comment Share on other sites More sharing options...
requinix Posted July 16, 2015 Share Posted July 16, 2015 ... How about sorting by the zone? Quote Link to comment Share on other sites More sharing options...
Troy2000 Posted July 16, 2015 Author Share Posted July 16, 2015 I tried this: ORDER BY zone ASC, Id DESC LIMIT 3 or just: ORDER BY zone, Id DESC LIMIT 3 Which gives me: Unit01 Unit01 Unit01 ORDER BY Id DESC LIMIT 3, zone ASC or just: ORDER BY Id DESC LIMIT 3, zone I get: error Undeclared variable: zone Am I not sorting by zone correctly? Thanks Troy Quote Link to comment Share on other sites More sharing options...
Solution Psycho Posted July 16, 2015 Solution Share Posted July 16, 2015 (edited) You are trying to use ORDER BY in two different ways - one to get the last three records that have been added and another one to sort them for display purposes - you can't do both concurrently. You can use a subquery to get the last three records and an outer query to sort them for display purposes SELECT * FROM ( SELECT Id, zone, xtime, xdate, temp, status FROM temp_log WHERE zone LIKE 'Unit%' ORDER BY Id DESC LIMIT 3 ) unitResults ORDER BY zone Edited July 16, 2015 by Psycho Quote Link to comment Share on other sites More sharing options...
Troy2000 Posted July 16, 2015 Author Share Posted July 16, 2015 (edited) Thanks Psycho! That was it. Sorry for my lack of knowledge in sql query. This works fine! Troy Edited July 16, 2015 by Troy2000 Quote Link to comment 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.