How to have "order by" ignore a row

jc94062

I've got a query that currently runs "order by sort" on it.

Fairly typical, but what I want to do it have it so that the first row always returned by the query is one with a specific id (or ids, the one(s) I want returned first are help in an array).

Is there any way in which this is possible, I can't seem to find anything about it.

Thanks

Lars_Berg

order by case when id in (3,4,5,6) then 0 else id end

replace 3,4,5,6 with your list of id's

jc94062

order by sort when id in (22817) then 0 else id end

order by sort when id !in (22817) then 0 else id end

Both the above end up returning 0 results.

I looked up when, in else, and end in the MySQL manual but got nothing...

Lars_Berg

It should be

order by case

not

order by sort

jc94062

order by case when id in (22817) then 0 else id end

returns it last (as order by sort does)

order by case when id !in (22817) then 0 else id end

I thought a nice negation would work, but that got rid of everything - zero results

order by case when id in (22817) then 10000 else id end

returns it last again

How does order by case work?
How does it know to order by sort except where the id is 22817?

Lars_Berg

I missed that you wanted it sorted by the column sort and not by id.

order by case when id in (22817) then 0 else sort end

a case expression is just a way of replacing one value with another. The value of the case expression will be 0 if id is equal to 22817 otherwise it will be the value of the column sort. This is covered in the Mysql documentation.

jc94062

Have you got a link for that documentation?

Is it at mysql.com? Because their search system seems to operate on an or statement, because the results I get from "order by case" contain the word order, OR by, OR case but not all 3 it seems.

order by case when id in (22817) then 0 else sort end

Just puts it at the bottom again,
I tried:
22817 -> 22817, 22801
then 0 -> then 999999999999999

and more, but it either stayed at the bottom of the list (this is a message board listing) or nothing appeared.

rrijnders

If the column you want to sort by is named "sort", then you will need to do something like this:

SELECT myField1, myField2, sort, (CASE WHEN sort='22817' THEN 0  ELSE sort END) AS tempSortField  FROM myTable ORDER BY tempSortField

Lars_Berg

case expressions:

http://www.mysql.com/doc/en/Control_flow_functions.html

What type of values does the sort column contain?

There should not be any problem about using an expression in an order by clause.

jc94062

I've tried everything I can think of with all the statements given and it still doesn't work.

The sort column contains numerical values, char(50)'s to order the posts in the board (I didn't design this particular board, my own board orders by a datetime field!)

I think I'm going to have to either setup a system to change the value or change the message boards to work off of a better system.

rrijnders

char(50) is character, not numeric. Are the values in that field left or right justified? Either way, the statement I posted above will work if you pad the comparison statement ("sort='blah') with the correct number of blanks.

jc94062

Can't believe I never realised that!
It should be just fine with a largeint type field in there.

Thanks all!