Query using ORDER BY 1

[chrissparkle]

I have a table of members with a country column in it (tinyint). The country column will either 1 through to 7 depending. I want to do a query which will order the members depending on the country the user is in (I have 7 domains for 7 different countries).

So if someone is at my Canada site, that's country "3". So I want my query to display members from Canada first, and then the rest in whatever order - doesnt matter. But members with "3" in the country column must come first.

Is this possible with ORDER BY?

 

[tobsen69]

You can try this using a UNION Statement. With that, you can start two queries, one only for the country, the user is in and one for all the others (ordered by whatever).

 

[r937]

order
    by case when country = 3
            then 0 else 937 end
     , whatever

r937.com | rudy.ca

 

[tobsen69]

Hi r937, could you please explain your idea. I don't get it...

 

[r937]

for those rows where the country code is 3, the major ORDER BY column will be 0

for all other rows, the major ORDER BY column will be 937

since 0 is less than 937, the rows with country code 3 will sort ahead of all other rows

the secondary ORDER BY column (whatever) is added just so that you can have another option to sort rows within each of the above two groups


r937.com | rudy.ca

 

[Alan0568]

Method 1
------
select * from yourtable
order by
case country when 3 then 0 else country end
------
Method 2
select * from yourtable where country = '3'
union all
select * from yourtable where country != '3'

 

[r937]

alan, your method 2 is not necessarily going to always work

without an ORDER BY, the database is free to return the final result set rows in whatever order it pleases -- in fact, i wouldn't be surprised if the optimizer realized that the two subqueries in the union actually return all rows except those where country is null, and decides just to do a single pass of the table

r937.com | rudy.ca

 

[chrissparkle]

i dont understand Alan's first method - how would that work when im passing in a different country value, ie "4". So if I pass in 4 into the query, I want all 4's to come out first, then the rest in whatever order.

 

[r937]

order
    by case when country = [b]@passed_in_value[/b]
            then 0 else 937 end
     , whatever

r937.com | rudy.ca