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]

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.