Page 1 of 1

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

Posted: Fri Nov 26, 2004 10:12 am
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

Posted: Fri Nov 26, 2004 10:40 am
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.

Posted: Fri Nov 26, 2004 1:35 pm
by Weirdan
You may find this thread ( http://lists.mysql.com/internals/9849 ) useful.