Re: 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.

>
> not in this order
> CD1, CD10, CD11, CD2.....
>
> thanks
>
> 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
> ________________________________________________________________________
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>