CODEWITHSUNDEEP
mysqlsql
Akash khan
Akash khan

Reputation: 979

SQL order by string order

In my table i have two file one is id another is strNum. I want to order the strNum like as

One
Two
Three

My table data

    id  strNum      
------  ------------
     1  Two         
     2  One         
     3  Five        
     4  Nine

I want to get output without any extra field add. How can i will order like as

One
Two
Three
Four
Five

Upvotes: 0

Views: 222

Answers (3)

mirmdasif
mirmdasif

Reputation: 6354

Checkout this http://sqlfiddle.com/#!4/6a54c/1

Create a table containing all the mappings from number string to integer value

TABLE_MAPING

Value   strNum
------  ------------
     1  One         
     2  Two         
     3  Three        
     4  Four 
.....................

Now create your query with appropriate join

SELECT m.strNum FROM my_table m 
  JOIN table_mapping as maping on m.strNum = maping.strNum
  ORDER BY maping.value

Upvotes: 0

Farshad
Farshad

Reputation: 1485

You can use this query : 

SELECT strNum FROM your_table
ORDER BY FIELD(strNum,'One','Two','Three','Four','Five','Six','Seven','Eight','Nine','Ten') ASC

Upvotes: 1

John
John

Reputation: 1852

You can use Case:

select strNum
from A
order by case when strNum='one' then 1
                    when strNum='two' then 2
                    when strNum='three' then 3
                    when strNum='four' then 4
                    when strNum='five' then 5
                    when strNum='six' then 6
                    when strNum='seven' then 7
                    when strNum='eight' then 8
                    when strNum='nine' then 9
                    when strNum='ten' then 10  end

You didn't mention which DB you are using. This is PostgreSQL version. See SQLFiddle

Upvotes: 2

Related Questions