MySQL Order_By columns of date and integer columns, but specify rules for ordering an integer column?

Question

I have the following table:

----------------------------------- PK integer date ----------------------------------- 1 2 03/01/01 2 1 04/01/01 3 3 02/01/01 4 3 05/01/01 5 2 01/01/01 6 1 06/01/01 

I want to place an order on a date column, BUT have dates with an integer 2 higher order than other integers. My conclusion will be like this.

 ----------------------------------- PK integer date ----------------------------------- 1 2 01/01/01 5 2 03/01/01 3 3 02/01/01 2 1 04/01/01 4 3 05/01/01 6 1 06/01/01 

At the moment, I do not know at all how to achieve this in MySQL, or even if it is possible. I haven’t tried anything yet, because I don’t know where to start.

I have to say that the order of integers that are not 2 is not a concern, so the following table is equally good.

 ----------------------------------- PK integer date ----------------------------------- 1 2 01/01/01 5 2 03/01/01 2 1 04/01/01 6 1 06/01/01 3 3 02/01/01 4 3 05/01/01 

Answer 1

You can order a query by a calculated expression, for example, case :

 SELECT * FROM `my_table` ORDER BY CASE `integer` WHEN 2 THEN 1 ELSE 0 END DESC, `date` ASC 

Answer 2

The easiest way to do this is to subtract integer by 2, and then get the absolute value of that number. Then collect on it. The absolute value of 2 - 2 will always be zero, and any other calculation will be greater than zero. so you insert an integer from 2 at the top of the list ( SQL Fiddle ):

 SELECT * FROM myTable ORDER BY ABS(`integer` - 2), `date` 

Answer 3

Doing this with UNION turned out to be more complicated than I thought, but you can specify priority:

 SELECT 1 as priority, id, mynumber, date from myTable WHERE mynumber = 2 UNION SELECT 2, id, mynumber, date from myTable WHERE mynumber <> 2 ORDER BY priority ASC, date ASC 

http://sqlfiddle.com/#!2/570be/16

Answer 4

You want to put integers in order of their minimum date, if I understand the question correctly.

You need to compute this field, connect it, and then use it to organize:

 select t.* from t join (select `integer`, min(date) as mindate from t group by `integer` ) tm on tm.`integer` = t.`integer` order by tm.mindate, integer; 

You can see the results of SQL Fiddle .

Answer 5

easy to handle it

EDIT:

  select * from Table1 order by case when `integer` != 2 then `date` end asc , case when `integer` = 2 then `date` end asc ; 

SECOND_DEMO