Geek asks and answers

ORDER A FIELD WITH A WILKARD?

Basically, I have a field that can contain some prefix strings:

word1
bla2
ttt3
word4 
[...]

I need to order SQL first, for example ttt3, and then all other lines after.
I tried it with no luck

ORDER BY FIELD (myField,'ttt3',*)

Any suggestions?

+3
source share
2 answers

Use a construct ORDER BY CASE. It forces 0 for the value you want to extract, and 1 for everything else, so it is sorted first 0. It just fits into the list ORDER BYlike any column separated by a comma, so you can add additional sorting columns later on.

ORDER BY
  CASE WHEN myField = 'ttt3' THEN 0 ELSE 1 END,
  myField,
  other_order_col,
  other_order_col2
+6
source 
ORDER BY myField = 'ttt3' DESC

or more general and vendor independent:

ORDER BY CASE WHEN myField = 'ttt3' THEN 0 ELSE 1 END
+2
source

More articles:


All Articles