Thread: natural sort order

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

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
________________________________________________________________________

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
>
>

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
________________________________________________________________________