CODEWITHSUNDEEP
mysqlsqlsql-order-by
Waldo Cosman
Waldo Cosman

Reputation: 33

Order by Field + alphabetically

I would like to order my query first By Field and then for the ones who were not in the list of 'by FIELD', sort alphabetically. When I do that, the result first gives all the fields that are not in the field list, en after those fields, the result shows the fields that were ordered by Field (correctly)

This is my query: 

SELECT merk FROM user WHERE merk <> '' group by merk 
ORDER BY FIELD(merk,'Theo', 'Anne et Valentin') ASC, merk ASC

Result is like this (see the last 2 are the ORDER BY FIELD records)

'Binoche'
'Binoche Mini'
'Blac'
'Carrera'
'Christian Dior'
'D&G'
'Staffan Preutz Design'
'Tim Van Steenbergen'
'Tom Ford'
'Tom Tailor'
'Tommy Hilfiger'
'Vogue'
'Vuillet Vega'
'You\'s Eyeworks'
'Theo'
'Anne et Valentin'

Upvotes: 2

Views: 211

Answers (2)

Michael Buen
Michael Buen

Reputation: 39393

You can also do it this way, so you don't have to change the sort order of the custom fields: http://www.sqlfiddle.com/#!2/c9122/1

SELECT merk , FIELD(merk,'Theo', 'Anne et Valentin') 
FROM user 
WHERE merk <> '' group by merk 
ORDER BY 
    COALESCE(
      NULLIF(FIELD(merk,'Theo', 'Anne et Valentin'), 0)
      , 99999)      
    ASC

Output:

|                  MERK | FIELD(MERK,'THEO', 'ANNE ET VALENTIN') |
------------------------------------------------------------------
|                  Theo |                                      1 |
|      Anne et Valentin |                                      2 |
|            Tom Tailor |                                      0 |
| Staffan Preutz Design |                                      0 |
|               Carrera |                                      0 |
|               Binoche |                                      0 |
|       You''s Eyeworks |                                      0 |
|        Tommy Hilfiger |                                      0 |
|   Tim Van Steenbergen |                                      0 |
|        Christian Dior |                                      0 |
|          Binoche Mini |                                      0 |
|                 Vogue |                                      0 |
|              Tom Ford |                                      0 |
|                   D&G |                                      0 |
|                  Blac |                                      0 |
|          Vuillet Vega |                                      0 |

Upvotes: -1

John Woo
John Woo

Reputation: 263693

it's because FIELD(merk,'Theo', 'Anne et Valentin') returns zero when the value is not on the list, so instead of FIELD(merk,'Theo', 'Anne et Valentin') ASC, order it by descending so the values that are present on the list of FIELD will be sorted first.

SELECT..
FROM..
WHERE....
ORDER BY FIELD(merk,'Theo', 'Anne et Valentin') DESC, merk ASC

SOURCEs

Upvotes: 2

Related Questions