开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜