tricky "order by" needs ... [SOLVED!!]

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
rhaynes
Forum Newbie
Posts: 18
Joined: Mon Nov 08, 2004 8:32 am

tricky "order by" needs ... [SOLVED!!]

Post by rhaynes »

Hi, I have a postgresql database which has a field which contains integers for the most part.
There are a few entries in the column which contain "numbers" like 193A , 1004C etc...
So I decided to declare that column type varchar(5). When I return queries of this database I would like some results ordered by number. So something like:

2
3
3A
3B
4
5A
5B
5C
6

however if I keep the data as varchar when I do the order 100 would follow 1 instead of 2 which I want. Doing an order by type casting to int doesn't work because of the entries like 3A. Anyone have any suggestions?

Thanks,
R Haynes
After a few responses a little more web searching and some trial error I found that the following work... this is not a robust method but works for my data:

Code: Select all

"order by substring(card_number from 'ї0-9]*')::int"
The way I understand it this strips the digit part of the variable off, converts it to an int and then sorts.

Thanks again,
R Haynes
Last edited by rhaynes on Sat Nov 27, 2004 11:25 am, edited 1 time in total.
jl
Forum Commoner
Posts: 53
Joined: Tue Nov 09, 2004 12:05 am

Post by jl »

The only way to do this that comes to mind straight away is to write a function inside postgres to parse your numbers and letters into a numerical value that you can sort by.

E.g. if you converted the letter to a value from 0.01 to 0.26 (assuming it's only A-Z you're working with) then you could add that value to the number, then use that final value to ORDER BY and things would come out in the right order:
e.g.

1A = 1.01
1B = 1.02
2 = 2
3F =3.06
100Z = 100.26

I don't know off the top of my head how to write this in pl/pgsql but I've done basic stuff like this before and I know it's possible. Once you'd defined the function inside pl/pgsql (or any other language you can write functions with in pg) your query would look like this (CONVERT_TO_NUMBER is the function you've defined)

SELECT foo FROM bar ORDER BY CONVERT_TO_NUMBER(your_weird_field) ASC ;

Hope that helps, check out the pg docs on pl/pgsql, it shouldn't be too hard.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

You may find this thread ( http://lists.mysql.com/internals/9849 ) useful.
Post Reply