natural sort order

On Fri, 2003-02-14 at 10:14, Ken Guest wrote:

Is it possible to use a natural sort order?
I want to get data back in the order
CD1, CD2, CD3., CD4, CD5, CD6, CD7, CD8, CD9, CD10, CD11

You have a couple options. The bandaid solutions is:

SELECT * FROM table ORDER BY substr(field, 3)::int

It might also be a good idea to look at why CD is prefixed to each. If
they're all CDs maybe just drop the prefix? You can always concat it on
the front for reports.

If there are different prefixes that probably means you should be
modelling it with two seperate fields. Just a thought.

Arguile wrote:

On Fri, 2003-02-14 at 10:14, Ken Guest wrote:

Is it possible to use a natural sort order?
I want to get data back in the order
CD1, CD2, CD3., CD4, CD5, CD6, CD7, CD8, CD9, CD10, CD11

You have a couple options. The bandaid solutions is:

SELECT * FROM table ORDER BY substr(field, 3)::int

It might also be a good idea to look at why CD is prefixed to each. If
they're all CDs maybe just drop the prefix? You can always concat it on
the front for reports.

That works well, though I had to add in a where clause because not all
entries are prefixed by 'CD',
so it's more of
select id from pricing where id like 'CD%' order by substr(id, 3)::int;

Thanks Arguile,

k.

________________________________________________________________________
This email has been scanned for all viruses by the MessageLabs SkyScan
service. For more information on a proactive anti-virus service working
around the clock, around the globe, visit http://www.messagelabs.com
________________________________________________________________________