vendredi 8 mai 2015

Sort group of records that are resulted from ORDER BY FIELD

In a MySQL table, I want to sort the group of records that I get by ORDER BY FIELD()

More specifically, Suppose, I ran the following query:

SELECT name,status,date FROM memberTickets ORDER BY FIELD(status,7,10,3,4) ASC

and got the following result:

---------------------------------------------------------
|    name    |    status    |            date           |
---------------------------------------------------------
|    A       |      7       |    2015-05-05 00:00:00    |
---------------------------------------------------------
|    B       |      7       |    2015-05-07 00:00:00    |
---------------------------------------------------------
|    C       |      7       |    2015-05-03 00:00:00    |
---------------------------------------------------------
|    D       |      10      |    2015-05-08 00:00:00    |
---------------------------------------------------------
|    E       |      10      |    2015-05-01 00:00:00    |
---------------------------------------------------------
|    F       |      10      |    2015-05-05 00:00:00    |
---------------------------------------------------------
|    G       |      10      |    2015-05-05 00:00:00    |
---------------------------------------------------------
|    H       |      3       |    2015-05-03 00:00:00    |
---------------------------------------------------------
|    I       |      3       |    2015-05-08 00:00:00    |
---------------------------------------------------------
|    J       |      3       |    2015-05-01 00:00:00    |
---------------------------------------------------------
|    K       |      4       |    2015-05-05 00:00:00    |
---------------------------------------------------------
|    l       |      4       |    2015-05-07 00:00:00    |
---------------------------------------------------------

Now, I want to sort the records in such an way so that the position of the group of records remain same(as set by order by field), but the records in each group are sorted with the date attribute as descending order, like the following:

---------------------------------------------------------
|    name    |    status    |            date           |
---------------------------------------------------------
|    A       |      7       |    2015-05-07 00:00:00    |
---------------------------------------------------------
|    B       |      7       |    2015-05-05 00:00:00    |
---------------------------------------------------------
|    C       |      7       |    2015-05-03 00:00:00    |
---------------------------------------------------------
|    D       |      10      |    2015-05-08 00:00:00    |
---------------------------------------------------------
|    E       |      10      |    2015-05-05 00:00:00    |
---------------------------------------------------------
|    F       |      10      |    2015-05-05 00:00:00    |
---------------------------------------------------------
|    G       |      10      |    2015-05-01 00:00:00    |
---------------------------------------------------------
|    H       |      3       |    2015-05-08 00:00:00    |
---------------------------------------------------------
|    I       |      3       |    2015-05-03 00:00:00    |
---------------------------------------------------------
|    J       |      3       |    2015-05-01 00:00:00    |
---------------------------------------------------------
|    K       |      4       |    2015-05-07 00:00:00    |
---------------------------------------------------------
|    l       |      4       |    2015-05-05 00:00:00    |
---------------------------------------------------------

Any idea or suggesions how to do that?

Aucun commentaire:

Enregistrer un commentaire