Jump to content

Sorting and/or grouping so certain status always at end


sKunKbad
Go to solution Solved by Barand,

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.

Edited by sKunKbad
Link to comment
Share on other sites

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 |
+----------+---------------------+---------+------+
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • 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.