Sorting and/or grouping so certain status always at end

[sKunKbad]

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

[Barand]

ORDER BY status='expired' , arrival_time

[sKunKbad]

I'd never guess that was possible. Thanks Barand.

[Barand]

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 |
+----------+---------------------+---------+------+

[sKunKbad]

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.