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