Re: Numeric Sorting

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