September 10th, 2012, 04:16 AM

Wrong order in firebird
Hello all,
I am having problem with sorting in firebird. I use two tables ms_matsta, dx_dokuln and left outer join with mp_matpoz and order it with mp_lokacx.
but i get unusual sort frist get A1, A10, A2 ... it should be A1, A2, A10.
How to get this correct order with mp_lokacx
Thank you for your help here is problematic SQL and results:
select dx_sifmat, mp_sifmat, mp_lokacx from ms_matsta, dx_dokuln left outer join mp_matpoz on mp_sifmat = dx_sifmat and mp_sifskl='418' where dx_kolmat !=0 and dx_sifdok='204180043' and ms_sifmat = dx_sifmat order by mp_lokacx;
DX_SIFMAT MP_SIFMAT MP_LOKACX
============= ============= =========
A102 A102 A1
A103 A103 A1
A100 A100 A10
A102 A102 A10
A103 A103 A10
A104 A104 A2
September 10th, 2012, 05:15 AM

If you know a collation that sorts the way you want try to use ORDER BY FIELD_NAME collate xxxxx.
Or first create your own collation with CREATE COLLATION
More information about that  here
September 10th, 2012, 01:07 PM

The problem is with your understanding of how alpha fields sort.
Remember those numbers in A10 are characters not numbers and
strings sort from left to right so
A1??????? will always come before A2.
You will need some special code to do what you want.
Clive
September 11th, 2012, 02:13 AM

@bundul: If the content always follows the same notation, e.g. only one character at the beginning and the rest are digits, you could split the string onthefly for the ORDER BY.
September 11th, 2012, 03:26 AM

I came with this solution but its not so universal if you got better idea please let me know.
Here is problematic sort:
select mp_lokacx from mp_matpoz where 1=1 order by mp_lokacx;
MP_LOKACX
=========
A1
A1
A1
A10
A10
A10
A11
A2
A3
good sorting:
select mp_lokacx from mp_matpoz where 1=1 order by cast(replace(mp_lokacx,'A','') as integer);
MP_LOKACX
=========
A1
A1
A1
A2
A3
A10
A10
A10
A11
But this is not so good because the data on mp_lokacx can be L1,L2 or some different pattern, if you got better idea let me know.
Thanks a lot
September 11th, 2012, 11:21 AM

Originally Posted by bundul
I came with this solution but its not so universal if you got better idea please let me know.
We need more information about the possible formats of the data.
Is the first character always alpha?
Is everything else always numbers?
If not, what are all the possibilities?
Clive
September 12th, 2012, 03:41 AM

Originally Posted by clivew
We need more information about the possible formats of the data.
Is the first character always alpha?
Is everything else always numbers?
If not, what are all the possibilities?
hi clive,
The input mask properties for mp_lokacx is Unfiltered so user can write anything.
So it could be any data.
Here is definition of field in mp_matpoz table:
MP_LOKACX VARCHAR(5) Not Null
September 12th, 2012, 03:59 AM

The user database is toooo old
I presume is Firebird Classic 1.0 or InterBase Classic 6.0 maybe lower version.
The qry
select mp_lokacx from mp_matpoz where 1=1 order by cast(replace(mp_lokacx,'A','') as integer);
wont work because there is not definition of functions cast with integer and replace. So i try with risky qry but this is not permanent solution:
select distinct mp_lokacx from mp_matpoz where 1=1 order by substring(mp_lokacx from 1 for 1), cast(trim(substring(mp_lokacx from 2 for 3)) as integer);
Please help, i need alternative more universal qry.
Thanks
September 12th, 2012, 02:06 PM

select mp_lokacx from mp_matpoz where 1=1 order by cast(replace(mp_lokacx,'A','') as integer);
First, the where clause achieves nothing here.
You can have an ORDER BY clause without a where clause.
Second replace(mp_lokacx,'A','') is useless as a general solution.
If, as you say, the field can contain anything there is nothing other than pure alpha
sorting that will work.
Given your criteria about what is possible, how in the world would you know the required
sort order never mind write code to sort these values?
A1
1A3RF
ab10
A20B
9
12345
Of course, if you have a set of rules that would define the sort
order for the above data, it would always be possible to retrieve
the unsorted data and then sort it on the client using a procedural language.
Clive