Re: Natural ordering in postgresql? Does it exist?

On Fri, Dec 10, 2004 at 02:47:53PM -0700, Clark Endrizzi wrote:

> I have a field that I'll be ordering and  I noticed that ordering is done
> logically and would confuse my users here (1,12,16,4,8, etc).

I think you mean that the ordering is done lexically and you want
it done numerically.  If the fields are entirely numeric then storing
them using one of the numeric types (INTEGER, NUMERIC, DOUBLE
PRECISION, etc.) will result in numeric sort orders.  If you have
all-numeric values in VARCHAR/TEXT fields, then you can cast them
to one of the numeric types in the ORDER BY clause:

SELECT ...
ORDER BY fieldname::INTEGER;

If the values are a mixture of text and numbers (e.g., ABC-1, ABC-12,
etc.) then you could use string functions to order different parts
of the field differently:

SELECT ...
ORDER BY SUBSTRING(fieldname, 1, 3),
         SUBSTRING(fieldname, 5)::INTEGER;

SELECT ...
ORDER BY SUBSTRING(fieldname FROM '^(\\w+)'),
         SUBSTRING(fieldname FROM '(\\d+)')::INTEGER;

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/