Sorting Text as a Number

Moderator: NorbertKrupa

Post Reply
sam
Newbie
Newbie
Posts: 14
Joined: Thu Sep 12, 2013 9:48 pm

Sorting Text as a Number

Post by sam » Fri Dec 27, 2013 8:07 pm

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

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1824
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Sorting Text as a Number

Post by JimKnicely » Fri Dec 27, 2013 8:26 pm

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!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica SQL”