Sort by Field

[KGodel]

Hello friends.

I am trying to sort the results of a MySQL query I am processing. Here is some background.

I have a list of possible values for a column. This list has 3 values that are always possible, the rest of the values can changed. I want to sort by this column, but in a specific way.

I know I can do

ORDER BY FIELD(bla, 'val1', 'val2'), bla ASC

My question is this. Of the three values that are always variable, I want two at the top of the sort order and one at the bottom. Is this possible? Basically, sort should look like this:

ForeverValue1
ForeverValue2
Variables ASC
..
..
ForeverValue3

(If you want to know why I want this its because I am generating a dynamic page but I want to use as few loops as possible. I could easily loop through these fields how I wanted in php but if I can sort them beforehand it will save me on code and pageload time.)

[kh99]

I didn't know about FIELD, that's pretty cool. Anyway, maybe this:

ORDER BY FIELD( bla,  'val2', 'val1' ) DESC , FIELD( bla,  'val3'), bla

[KGodel]

You cannot use ASC or DESC after the FIELD sort, you have to use the field again and then use the ASC or DESC, but that will catch the value I want excluded and saved for last. :/ If I add another FIELD sort it will have already been sorted right?

[kh99]

Originally Posted by KGodel

You cannot use ASC or DESC after the FIELD sort, you have to use the field again and then use the ASC or DESC, but that will catch the value I want excluded and saved for last. :/ If I add another FIELD sort it will have already been sorted right?

Well, I tested what I posted. But are you saying you can't because it doesn't do what you want? Then I guess maybe I didn't understand what you wanted.

[KGodel]

I basically have three values I want excluded from the typical alphabetical sorting of ORDER BY. I want 2 values to be sorted first and one value to be sorted last. The others change so I just want them to have a normal sort between the 2 first values and the last value. Instead of:

A
B
C
...
Z

Where val1, val2, and val3 are included, I want:

val1
val2
A
B
C
...
Z
val3

[kh99]

I thought that's what I did. You say above that you can't have ASC or DESC after FIELD, but I think you can. In fact if you don't it just defaults to ASC anyway.

[KGodel]

I tried what you wrote and it ended up shoving Val3 to the top no matter what. If I don't add DESC to the val1 and val2 then they don't show up at the top either, so I basically need a DESC, ASC, then DESC (to be sure val3 shows at the bottom) but it seems that if the middle is ASC then val3 gets caught.

But I though of an alternate way. I could simply create a string in the order I want and JUST do a field thing instead of handling it in the MySQL

[kh99]

Hmm...strange. I made a test table with color names and whatever I put as 'val1' and 'val2' end up at the top, and 'val3' ends up at the bottom, and the rest end up between, sorted alphabetically. But I guess there must be something different about your data or something.

Anyway, at least you figured out a way.