Thread: Natural ordering in postgresql? Does it exist?
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
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/>
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/
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
"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