Sorting Text as a Number

[sam]

Hello,

Is there an option on the order by statement that I can use to sort text values like numbers?

I have a varchar column in a table like this:

Code: Select all

dbadmin=> \d tsort;
                                    List of Fields by Tables
  Schema  | Table | Column |    Type     | Size | Default | Not Null | Primary Key | Foreign Key
----------+-------+--------+-------------+------+---------+----------+-------------+-------------
 snowfall | tsort | num    | varchar(10) |   10 |         | f        | f           |
(1 row)

Here's what I get when I use an order by:

Code: Select all

dbadmin=> select * from tsort order by num;
 num
------
 10
 100
 1000
 150
 200
 250
 300
 350
 400
 50
 950
(11 rows)

But I want the results to be:

10
50
100
150
200
250
300
350
400
950
1000

Thank you

[JimKnicely]

Hi Sam,

Simply convert the VARCHAR to an INT in the ORDER BY clause. All of these methods will do the trick:

• select * from tsort order by num::int;
  select * from tsort order by to_number(num);
  select * from tsort order by cast (num as int);

Hope this helps!