#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    4
    Rep Power
    0

    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
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    224
    Rep Power
    12
    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
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,055
    Rep Power
    383
    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
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2010
    Posts
    43
    Rep Power
    4
    @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.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    4
    Rep Power
    0
    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
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,055
    Rep Power
    383
    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
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    4
    Rep Power
    0
    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
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    4
    Rep Power
    0
    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
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,055
    Rep Power
    383
    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

IMN logo majestic logo threadwatch logo seochat tools logo