CODEWITHSUNDEEP
mysql
user2301515
user2301515

Reputation: 5117

Sorting special name to end in database

I want to sort a mysql-table

select id,name from tbl order by name asc;

returns

1  name1
2  name2
4  name3
5  name4
8  name5

How to order that e.g name 3 goes to end of the table like

select id,name from tbl order by ["name is name3????"],name asc;

returns

1  name1
2  name2
5  name4
8  name5
4  name3

Thank you

Upvotes: 1

Views: 76

Answers (1)

GolezTrol
GolezTrol

Reputation: 116110

With case you can return a value based on a condition. So you can return 1 for name3 and 0 for other names. Primarily sort on this value to put name3 in the back. The secondary sort value is the name, so that the other names are still sorted alphabetically.

select 
  id, name 
from 
  tbl 
order by 
  case when name = 'name3' then 1 else 0 end,
  name

Strawberry just taught me in the comment that you could also use the function field to accomplish this. This is especially handy and more compact, if you want to specify a specific sort for a number of names. Using case that would quickly become bulky. Field returns the index of the first string in the list of other strings, so field(name, 'name3', 'name4') would return 1 for 'name3' and 2 for 'name4' and 0 for all other names.

Your query would then look like this:

select 
  id, name 
from 
  tbl 
order by 
  field(name, 'name3'),
  name

Upvotes: 4

Related Questions