MYSQL multiple ORDER BY fields

[Troy2000]

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

 

[requinix]

...

 

How about sorting by the zone?

[Troy2000]

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

[Psycho]

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

[Troy2000]

Thanks Psycho!

 

That was it.  Sorry for my lack of knowledge in sql query.  This works fine!

 

Troy

Edited July 16, 2015 by Troy2000