CODEWITHSUNDEEP
mysqlfilterclause
Matt Alice
Matt Alice

Reputation: 51

SQL: using clause with "ORDER BY"

I have a list of orders in a SQL table, and for each order I have a ship-to country code.

example:

order     country_code
X00145    GB
X00178    FR
D55454    AL
E54566    GB
F59565    IE
O84849    ES
K54545    US
N61465    GB
W65478    GB

I am trying to sort the output so orders that are not going to country_code GB are returned first, alphabetically , and then have the GB orders come last (like after IE and US in my example above).

Using "ORDER BY" only lets me filter with ASC or DESC. Is there a way to use a clause along with "ORDER BY"? (I guess not because I could not find anything online)?

Upvotes: 0

Views: 47

Answers (3)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44864

You can use order by field()

select * from table_name
order by field(country_code,'GB') desc,country_code

DEMO

Upvotes: 0

Ronak Shah
Ronak Shah

Reputation: 1549

try this:

select * from yourtable where  country_code != 'GB' order by country_code

Union 

select * from yourtable where  country_code = 'GB'

Upvotes: 0

juergen d
juergen d

Reputation: 204884

For MySQL you could do

select * from your_table
order by country_code = 'GB', 
         country_code,
         `order`

or generally

select * from your_table
order by case when country_code <> 'GB' then 1 else 2 end, 
         country_code,
         `order`

Upvotes: 1

Related Questions