SQL order by string order

Question

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

I'm confused, You don't have `Four` in the table.. So how is it in the result? You just wish to print a list of numbers names without any relation to the table? — John, Aug 30 '15 at 05:56
If I understand the question correctly, he's wanting order string number names by their numeric value. — Shawn Conn, Aug 30 '15 at 05:59
possible duplicate of [SQL order string as number](http://stackoverflow.com/questions/11808573/sql-order-string-as-number) — Shawn Conn, Aug 30 '15 at 06:00
@ShawnConn I got that too.. I just don't understand his result. I thought the desired result according to the table is: `One,Two,Five,Nine.` — John, Aug 30 '15 at 06:02

score 2 · Answer 1 · answered Aug 30 '15 at 06:12

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

Farshad · Accepted Answer · 2015-08-30T06:14:57.077

1

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

edited Aug 30 '15 at 06:14

answered Aug 30 '15 at 06:09

Farshad

1,465
1
9
12

2

I'm interested in which flavour of SQL has the `FIELD` function? – Nick.Mc Aug 30 '15 at 06:43
`Field` is a `mysql` function. – Farshad Aug 30 '15 at 09:10

mirmdasif · Answer 3 · 2015-08-30T07:23:28.643

0

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

edited Aug 30 '15 at 07:23

answered Aug 30 '15 at 06:15

mirmdasif

6,014
2
22
28

In that case he doesn't really need a new table. He can add a column to his existed table to do the mapping. – John Aug 30 '15 at 06:16
This is much easier solution with a pre-populated table of mappings. – mirmdasif Aug 30 '15 at 06:18
1

It's another approach to solve the OP question. I just said that you can get the same result by adding a column to his table rather then create a new table. Alter the existed table to be: `(id , strNum , value)`. You don't need two tables as you suggested: `(id , strNum)` `(strNum , value)`. – John Aug 30 '15 at 06:22

SQL order by string order

3 Answers3