From: | Michael Glaesemann <grzm(at)seespotcode(dot)net> |
---|---|
To: | Chester <chester(at)hica(dot)com(dot)au> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Numeric Sorting |
Date: | 2007-06-07 04:30:40 |
Message-ID: | C49B32BB-BE1C-4D67-85E6-8E15F01C467C@seespotcode.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Jun 6, 2007, at 21:48 , Chester wrote:
> Can you numerically sort a set of integers stored in a char column,
> I guess change the cast of the column for the "sort by" process.
That's pretty much what you have to do, I believe.
SELECT *
FROM ints_as_chars
ORDER BY int_as_char;
int_as_char
-------------
1
10
2
20
3
30
4
40
(8 rows)
SELECT *
FROM ints_as_chars
ORDER BY to_number(int_as_char, '99');
int_as_char
-------------
1
2
3
4
10
20
30
40
(8 rows)
If you do it a lot, you might consider creating a view with a virtual
column that has the cast done for you.
CREATE VIEW ints_as_ints AS
SELECT *, to_number(int_as_char,'99') AS int_as_int
FROM ints_as_chars;
CREATE VIEW
SELECT *
FROM ints_as_ints
ORDER BY int_as_char;
int_as_char | int_as_int
-------------+------------
1 | 1
10 | 10
2 | 2
20 | 20
3 | 3
30 | 30
4 | 4
40 | 40
(8 rows)
SELECT *
FROM ints_as_ints
ORDER BY int_as_int;
int_as_char | int_as_int
-------------+------------
1 | 1
2 | 2
3 | 3
4 | 4
10 | 10
20 | 20
30 | 30
40 | 40
(8 rows)
Michael Glaesemann
grzm seespotcode net
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2007-06-07 07:46:51 | Re: formatting the output of a function |
Previous Message | Chester | 2007-06-07 02:48:14 | Numeric Sorting |