CODEWITHSUNDEEP
mysql
JVMX
JVMX

Reputation: 1078

Arbitrary Ordering From two fields in MySql

I have a difficult sort to do. I need to be able to sort the query results in an arbitrary manner.

Here is the Schema:

CREATE  TABLE `Wines` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `Vinyard` VARCHAR(45) NULL ,
  `Quality` VARCHAR(45) NULL ,
  `Sell_By` DATE NULL ,
  PRIMARY KEY (`ID`) );


INSERT INTO `Wines` (`Vinyard`, `Quality`, `Sell_By`) VALUES ('Draper', 'High', '2012-03-22');
INSERT INTO `Wines` (`Vinyard`, `Quality`, `Sell_By`) VALUES ('Draper', 'Medium', '2014-07-16');
INSERT INTO `Wines` (`Vinyard`, `Quality`, `Sell_By`) VALUES ('Draper', 'Medium', '2012-01-01');
INSERT INTO `Wines` (`Vinyard`, `Quality`, `Sell_By`) VALUES ('Becks', 'High', '2014-07-16');
INSERT INTO `Wines` (`Vinyard`, `Quality`, `Sell_By`) VALUES ('Becks', 'Ultra', '2013-02-02');
INSERT INTO `Wines` (`Vinyard`, `Quality`, `Sell_By`) VALUES ('Stevens', 'Crap', '2014-08-16');
INSERT INTO `Wines` (`Vinyard`, `Quality`, `Sell_By`) VALUES ('Stevens', 'Medium', '2014-01-01');
INSERT INTO `Wines` (`Vinyard`, `Quality`, `Sell_By`) VALUES ('Stevens', 'Low', '2013-09-13');
INSERT INTO `Wines` (`Vinyard`, `Quality`, `Sell_By`) VALUES ('Miller', 'Low', '2014-01-01');

Now I know I can get a simple sort of Quality (that is not alphabetical) as follows:

 SELECT *,
CASE Quality
WHEN 'Ultra' THEN 1
WHEN 'High' THEN 2
WHEN 'Medium' THEN 3
WHEN 'Low' THEN 4
WHEN 'Crap' THEN 5
ELSE 6
END AS myORDER
FROM Wines
ORDER BY myOrder

That Gives Me

ID  Vinyard Quality Sell_By myORDER
5   Becks   Ultra   2013-02-02  1
1   Draper  High    2012-03-22  2
4   Becks   High    2014-07-16  2
2   Draper  Medium  2014-07-16  3
3   Draper  Medium  2012-01-01  3
7   Stevens Medium  2014-01-01  3
8   Stevens Low 2013-09-13  4
9   Miller  Low 2014-01-01  4
6   Stevens Crap    2014-08-16  5

OK So Far so good. Now the Wrinkle.

I need to move the Medium Quality wines and only the Medium quality wines Whose Sell by date has passed to the end. SO the result would be

ID  Vinyard Quality Sell_By
5   Becks   Ultra   2013-02-02
1   Draper  High    2012-03-22
4   Becks   High    2014-07-16
2   Draper  Medium  2014-07-16
7   Stevens Medium  2014-01-01
8   Stevens Low 2013-09-13
9   Miller  Low 2014-01-01
6   Stevens Crap    2014-08-16
3   Draper  Medium  2012-01-01  <--Notice this one moved

How can I do this?

Upvotes: 0

Views: 28

Answers (3)

showdev
showdev

Reputation: 29178

I suggest the following query:

SELECT *,
CASE Quality
WHEN 'Ultra' THEN 1
WHEN 'High' THEN 2
WHEN 'Medium' THEN 3
WHEN 'Low' THEN 4
WHEN 'Crap' THEN 5
ELSE 6
END AS myOrder,
IF(`Quality`='Medium' AND `Sell_By`<NOW(),`Sell_By`,NULL) as `med_sell_by`
FROM Wines
ORDER BY ISNULL(`med_sell_by`) DESC,`med_sell_by` ASC, myOrder ASC;

This will return an additional column which, if the quality is "medium" and sell_by has passed, contains the sell_by date for sorting to the bottom. If those criteria are not met, the additional field will be NULL and sorted to the top.

SQLFIDDLE

Upvotes: 0

Explosion Pills
Explosion Pills

Reputation: 191779

ORDER BY Quality = 'Medium' AND Sell_By < CURDATE(), myOrder

This will order by both conditions.

Upvotes: 4

τεκ
τεκ

Reputation: 3114

order by 
  CASE WHEN Quality = 'Medium' 
    and Sell_By < GETDATE() THEN 0 
    ELSE 1 END, 
  myOrder

or move the equivalent case inside myOrder. Quality shouldn't be a text column, by the way.

Upvotes: 0

Related Questions