Natural ordering in postgresql? Does it exist?

"Clark Endrizzi" <clarkendrizzi@hotmail.com> writes:

Hi all,
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).

Sounds like you're storing a number in a text field. Numeric fields
sort in numerical order.

-Doug

Numbers as text are ordered like that. Integers are ordered as you would like.

The best hack I have seen if you are stuck with text is

...order by length(numbers_as_test), numbers_as_text

which sorts first by number of "digits" then by text order.

Ian Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
iharding@tpchd.org
Phone: (253) 798-3549
Pager: (253) 754-0002

"Clark Endrizzi" <clarkendrizzi@hotmail.com> 12/10/04 1:47 PM >>>

Hi all,
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'm writing an application in PHP that connects to Postgres and while I know
that PHP has some powerful natural ordering functions it would be much
easier if I could just use something from postgres directly. Does there
exist any way to order naturally?

Thanks,
Clark Endrizzi

_________________________________________________________________
Is your PC infected? Get a FREE online computer virus scan from McAfee®
Security. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

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/

Quoth clarkendrizzi@hotmail.com ("Clark Endrizzi"):

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'm writing an application in PHP that connects to Postgres and while
I know that PHP has some powerful natural ordering functions it would
be much easier if I could just use something from postgres directly.
Does there exist any way to order naturally?

If you wish to impose an ordering on an SQL query, you must specify
that ordering using an "ORDER BY" clause.

That's not a PostgreSQL issue; that's how SQL works.
--
output = ("cbbrowne" "@" "gmail.com")
http://linuxfinances.info/info/linux.html
Rules of the Evil Overlord #204. "I will hire an entire squad of blind
guards. Not only is this in keeping with my status as an equal
opportunity employer, but it will come in handy when the hero becomes
invisible or douses my only light source."
<http://www.eviloverlord.com/&gt;