Select parameter

[omoutop]

Hello everybody,

I have a table called locations in the following structure:

location_id, name, location order.

I want to select them according to the location_order BUT I need a specific location first despite the fact that its order is not to be in the first place...

any thoughts?

Thanks in advance

[dreamcatcher]

How about coding the one you want first manually, then running the clause to fetch all rows except that one?

dc

[omoutop]

hello dreamcatcher,

I have been doing that for a long time and I am just curious if there is a way to do everything in one query, I have tried everything and searched alot the last 2 hours, tried limits, WHERE's but nothing, I wish I had a good idea to do that in just one query....as long as i know mysql does not support that...

I'll keep you posted if I find something

thx

[vincevincevince]

ORDER BY location_order=34,location_order

This will put 'location_order=34' first, and then all the rest will be returned.

Why? location_order=34 evaluates to true, which is higher than location_order!=34 (rest of the rows) which evaluates to false.

location_order ¦ location_order=34 ¦ location_order
----------------------------------------------------
34____________ ¦ true_____________ ¦ 34_____________
4_____________ ¦ false____________ ¦ 4______________
8_____________ ¦ false____________ ¦ 8______________
10____________ ¦ false____________ ¦ 10_____________
99____________ ¦ false____________ ¦ 99_____________

Having sorted your one row to the top, it then sorts within the two groups to break the false=false=false etc.. tie-breaker between the rest of the rows, by using the ,location_order second ORDER BY argument.

[omoutop]

this is what i tried and works fine:

"select * from areas order by area_id = 13 desc, area_order"...

thank you very much both of you!