Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2012
    Posts
    3

    Unanswered: Firebird ordering problem please help

    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
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    The rows are sorted alphabetically.
    What version of Firebird are you using?
    Only the first character is alphabetic, or there may be other alphabetic characters in column mp_lokacx?

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you want A1 to be followed by A2, ratrher than A10 then you need to change the way you store the information OR change the informatiomn itself
    ie use A01, A2 and so on.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Sep 2012
    Posts
    3
    Hi to all

    i was thinking of changing data but i cant because user dont let me.

    Version of database, i cant see with

    SELECT rdb$get_context('SYSTEM', 'ENGINE_VERSION') from rdb$database;

    so i presume is Firebird Classic 1.0 or InterBase Classic 6.0 maybe lower version.

    I checked about alphabetic pattern in mp_lokacx and there is also different letters. L1, L2 ...

    Thanks

  5. #5
    Join Date
    Sep 2012
    Posts
    3
    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

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by bundul View Post
    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.
    yes
    fix the data or store it in a more appropriate mechanism
    otherwise you are going to be forced to use string manipulation of variable length elements, never nice, always going to be a performance issue

    bear in mind that you customer expects / wants to see data in a particular format, that doesn't mean you have to store it in that format.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •