PGSQL GENERAL 41 NATURAL ORDERING IN POSTGRESQL DOES IT EXIST
From: clarkendrizzi@no-spam ("Clark Endrizzi")
Subject: Natural ordering in postgresql? Does it exist?
Date: Fri, 10 Dec 2004 14:47:53 -0700


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





From: doug@no-spam (Doug McNaught)
Subject: Re: Natural ordering in postgresql? Does it exist?
Date: Fri, 10 Dec 2004 19:36:22 -0500

"Clark Endrizzi" <clarkendrizzi@no-spam> 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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

From: mike@no-spam (Michael Fuhr)
Subject: Re: Natural ordering in postgresql? Does it exist?
Date: Fri, 10 Dec 2004 18:38:33 -0700

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/

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

From: Christopher Browne (cbbrowne@no-spam)
Subject: Re: Natural ordering in postgresql? Does it exist?
Date: 11 Dec 2004 04:31:15 GMT

Quoth clarkendrizzi@no-spam ("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" "@no-spam" "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/>