ORDER BY FIELD() and duplicate data
Lets say I have the following query.
SELECT stringdata FROM table ORDER BY FIELDS( stringdata, 'tg','nk','mg','pl') asc;
For some reason I'm getting the results at the very bottom. How can I get the query to put the results starting from 'tg' at row 1 rather than the last row in the results?
Not only that but there's more than one 'tg' in the data, I'd like it to sort it in this expected output:
stringdata
__________
'tg'
'tg'
'tg'
'nk'
'nk'
'mg'
开发者_开发技巧 'mg'
'mg'
'pl'
So far using ORDER BY Fields()
is only sorting one instance of the data rather than all.
Using desc
instead of asc
in the query works as expected. I get 'pl'
on the first row, then 'mg'
, 'nk'
, etc.
Normally the FIELD
clause in ORDER BY
works something like
SELECT * FROM table ORDER BY FIELD(field, high_priority, second_high,
,....., low_priority);
So in your query, the sorting took place as you mentioned and when you gave the ASC
it printed from the lowest_priority. So, for your case, if you want tg
at the top, you can either reorder the priority in the FIELDS
or as you have already tried use desc
If you want first rows with 'tg'
, then rows with .... then with 'pl'
and then all the rest sorted (ASC
or DESC
) use this:
SELECT stringdata
FROM table
ORDER BY FIELD( stringdata, 'pl','mg', 'nk', 'tg') DESC
, stringdata ASC --- or DESC
精彩评论