sKunKbad Posted July 10, 2015 Share Posted July 10, 2015 (edited) Let's say that I have a table for vegetables, and the vegetables are normally sorted by the date they arrived at the store. There is also a status, where the vegetable can be acceptable, good, great, special, rotten, etc. So if I were to query for the vegetables, sorted by the arrival time, it would be a simple sort by time. What if I always want to ensure that vegetables of a certain status are at the end of the returned set of rows? Expected result: Carrot, 2015-7-10 08:00:00, rotten Broccoli, 2015-7-10 07:00:00, great Radish, 2015-7-10 06:00:00, good Lettuce, 2015-7-10 05:00:00, acceptable Pepper, 2015-7-10 05:00:00, good Potato, 2015-7-10 09:00:00, expired Tomato, 2015-7-10 08:00:00, expired Turnip, 2015-7-10 07:00:00, expired I just used vegetables as an example, but what I wanted to show was that although sorted by date, the vegetables with a status of expired are at the end of the list. I can't sort by status, because status is not guaranteed to be alpha or numeric or anything else. Also, I need to be able to paginate this, so how do I get those with special status (expired in this case) at the end? Sorry, I'm not especially good with MySQL. Edited July 10, 2015 by sKunKbad Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted July 10, 2015 Solution Share Posted July 10, 2015 ORDER BY status='expired' , arrival_time 1 Quote Link to comment Share on other sites More sharing options...
sKunKbad Posted July 10, 2015 Author Share Posted July 10, 2015 I'd never guess that was possible. Thanks Barand. Quote Link to comment Share on other sites More sharing options...
Barand Posted July 10, 2015 Share Posted July 10, 2015 Explanation: The boolean expression "Status='expired'" evaluates to 0 or 1 SELECT variety , expiry , status , status='expired' as demo FROM vegetable ORDER BY status='expired', expiry; +----------+---------------------+---------+------+ | variety | expiry | status | demo | +----------+---------------------+---------+------+ | sprouts | 2015-07-05 21:53:57 | fresh | 0 | | cabbage | 2015-07-10 21:53:57 | rotten | 0 | | lettuce | 2015-07-11 21:53:57 | fresh | 0 | | cucumber | 2015-07-12 21:53:57 | rotten | 0 | | potato | 2015-07-08 21:53:57 | expired | 1 | | totmato | 2015-07-14 21:53:57 | expired | 1 | +----------+---------------------+---------+------+ Quote Link to comment Share on other sites More sharing options...
sKunKbad Posted July 10, 2015 Author Share Posted July 10, 2015 Thanks for explaining. Do you know of any good MySQL books? I've read basic ones that relate to PHP, and I have another MySQL book. They don't go over cool stuff like this. 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.