Numeric Sorting
Hi
Just a quick one, I have been around Postgres for a little while and
have never found an answer to this.
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.
and, if you can how...Unfortunatly I have no choice in the current
"char" definition of the column.
The version of PSQL is 8.+
TIA
Regards
C
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