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

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

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

@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 on-the-fly for the ORDER BY.

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

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

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

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

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