Jump to content

Sorting and/or grouping so certain status always at end


sKunKbad

Recommended Posts

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.

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.