[MySQL] ORDER BY with fixed values

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • Dark Rayden

    [MySQL] ORDER BY with fixed values

    Hi!

    I recently got a strange problem and I have no idea on the solution.

    I try to do a ORDER BY statement with a fixed order of values, because
    my client want's it this way.

    My approach is like this:

    $the_row = "'A', 'B', 'B b', 'C', 'D d', 'E'";

    $q1 = mysql_query("SE LECT DISTINCT foo FROM $table WHERE bar =
    '$kat_array[$bart]' ORDER BY $the_row") or die ("ERROR while 'SELECT'
    all sub_kategorie from db! ->".mysql_error( ));

    Now, what I get is not the expected order of
    A
    B
    B b
    C
    D d
    E

    but something like
    A
    C
    B b
    E
    D d

    The result is not reflecting the order given by $the_row. It seems, it
    orders some elements wrong. I tried several variations of the query
    but nothing worked. I also tried it directly in phpMyAdmin with the
    same, wrong result.

    So my question is:
    Is this a mistake done by me?
    Is this a mistake in the MySQL query, meaning, it can't output a given
    order?
    Did I miss anything?

    I hope someone can help.

    Regards,
    Rayden
  • Felix Geerinckx

    #2
    Re: [MySQL] ORDER BY with fixed values

    On 19/04/2005, Dark Rayden wrote:
    [color=blue]
    > I try to do a ORDER BY statement with a fixed order of values, because
    > my client want's it this way.
    >
    > My approach is like this:
    >
    > $the_row = "'A', 'B', 'B b', 'C', 'D d', 'E'";
    >
    > $q1 = mysql_query("SE LECT DISTINCT foo FROM $table WHERE bar =
    > '$kat_array[$bart]' ORDER BY $the_row") or die ("ERROR while 'SELECT'
    > all sub_kategorie from db! ->".mysql_error( ));[/color]

    Your ORDER BY clause is the same for all records, so nothing happens.
    You need something like:

    ORDER BY CASE foo
    WHEN 'A' THEN 1
    WHEN 'B' THEN 2
    WHEN 'B b' THEN 3
    WHEN 'C' THEN 4
    WHEN 'D d' THEN 5
    WHEN 'E' THEN 6
    ELSE 999
    END

    which, in your particular example, is the same as

    ORDER BY foo (alphabetically )

    --
    felix

    Comment

    • Dark Rayden

      #3
      Re: [MySQL] ORDER BY with fixed values

      On Tue, 19 Apr 2005 11:13:28 GMT, "Felix Geerinckx"
      <felix.geerinck x@gmail.com> wrote:
      [color=blue]
      >On 19/04/2005, Dark Rayden wrote:
      >[color=green]
      >> I try to do a ORDER BY statement with a fixed order of values, because
      >> my client want's it this way.
      >>
      >> My approach is like this:
      >>
      >> $the_row = "'A', 'B', 'B b', 'C', 'D d', 'E'";
      >>
      >> $q1 = mysql_query("SE LECT DISTINCT foo FROM $table WHERE bar =
      >> '$kat_array[$bart]' ORDER BY $the_row") or die ("ERROR while 'SELECT'
      >> all sub_kategorie from db! ->".mysql_error( ));[/color]
      >
      >Your ORDER BY clause is the same for all records, so nothing happens.
      >You need something like:
      >
      >ORDER BY CASE foo
      > WHEN 'A' THEN 1
      > WHEN 'B' THEN 2
      > WHEN 'B b' THEN 3
      > WHEN 'C' THEN 4
      > WHEN 'D d' THEN 5
      > WHEN 'E' THEN 6
      > ELSE 999
      >END
      >
      >which, in your particular example, is the same as
      >
      >ORDER BY foo (alphabetically )[/color]

      Thanks Felix for trying to help me.

      Just to eliminate any mistake: the wanted order should NOT be
      alphabetical. This was just to illustrate the problem I got. The
      entries in the string aren't alphabetical (that's why my problem is
      existent &) ).

      First, I show you, what I mean.
      My original string is this:
      $art1 = "'Welschrieslin g', 'Grüner Veltliner', 'Riesling', 'Sauvignon
      blanc', 'Weißburgunder' , 'Chardonnay', 'Muskat Ottnonel', 'Gelber
      Muskatteller', 'Cuvée von Weißweinen', 'Pannobile weiß', 'Exquisites
      weiß'";

      The output of the query is this:
      Welschriesling | Riesling | Sauvignon blanc | Weißburgunder |
      Chardonnay | Muskat Ottonel | Cuvée von Weißweinen | Pannobile weiß |
      Grüner Veltliner | Magnums weiß | Exquisites weiß | Gelber
      Muskatteller

      Notice the entry "Grüner Veltliner" isen't in the 2nd place, but in
      the 9th. And the same goes for some other entrys like "Gelber
      Muskatteller" or "Pannoble weiß".

      Second, isen't there any 'easier' way as to do a CASE option?
      Because if something changes in the string, the CASE has to be
      changed, too. A bit tedious I think.

      Regards,
      Rayden

      Comment

      • Felix Geerinckx

        #4
        Re: [MySQL] ORDER BY with fixed values

        On 19/04/2005, Dark Rayden wrote:
        [color=blue]
        > Just to eliminate any mistake: the wanted order should NOT be
        > alphabetical. This was just to illustrate the problem I got. The
        > entries in the string aren't alphabetical (that's why my problem is
        > existent &) ).[/color]

        That's what I thought ;-)

        [color=blue]
        > Second, isen't there any 'easier' way as to do a CASE option?
        > Because if something changes in the string, the CASE has to be
        > changed, too. A bit tedious I think.[/color]

        Not as far as I know. But since you are creating your SQL statement in
        code anyway, it would not be too dificult to generate the CASE-part as
        well?


        --
        felix

        Comment

        • Dark Rayden

          #5
          Re: [MySQL] ORDER BY with fixed values

          On Tue, 19 Apr 2005 12:53:57 GMT, "Felix Geerinckx"
          <felix.geerinck x@gmail.com> wrote:
          [color=blue]
          >On 19/04/2005, Dark Rayden wrote:
          >[color=green]
          >> Just to eliminate any mistake: the wanted order should NOT be
          >> alphabetical. This was just to illustrate the problem I got. The
          >> entries in the string aren't alphabetical (that's why my problem is
          >> existent &) ).[/color]
          >
          >That's what I thought ;-)
          >[/color]
          OK, just wanted to make sure. ;)[color=blue]
          >[color=green]
          >> Second, isen't there any 'easier' way as to do a CASE option?
          >> Because if something changes in the string, the CASE has to be
          >> changed, too. A bit tedious I think.[/color]
          >
          >Not as far as I know. But since you are creating your SQL statement in
          >code anyway, it would not be too dificult to generate the CASE-part as
          >well?[/color]

          Of course, you're right. But I'm lazy by nature, so I asked. ;)

          Just one thing:
          How would you create those CASE statements?
          I mean, if the order should be lik A, B, C and the output is like A,
          C, B, how would I construct a statement to make sure, all of the cases
          are in the query in the order I expect it?

          Sorry if I bother you to go into more detail. :/

          Regards,
          Rayden

          Comment

          • Felix Geerinckx

            #6
            Re: [MySQL] ORDER BY with fixed values

            On 19/04/2005, Dark Rayden wrote:
            [color=blue]
            > How would you create those CASE statements?
            > I mean, if the order should be lik A, B, C and the output is like A,
            > C, B, how would I construct a statement to make sure, all of the cases
            > are in the query in the order I expect it?[/color]

            If we go back to the original example:

            ORDER BY CASE foo
            WHEN 'A' THEN 1
            WHEN 'B' THEN 2
            WHEN 'B b' THEN 3
            WHEN 'C' THEN 4
            WHEN 'D d' THEN 5
            WHEN 'E' THEN 6
            ELSE 999
            END

            It's the integers 1, 2, ... that are important: when foo = 'A' you get
            1, when foo = 'D d' you get 5. Since 1 < 5, 'A' will come before 'D d'
            when you ORDER.

            Also note that there is another form of CASE, where you can say e.g.

            -- Order alphabetically, but G and J first
            ORDER BY CASE
            WHEN foo LIKE 'G%' THEN 1
            WHEN foo LIKE 'J%' THEN 2
            ELSE 999
            END

            Between WHEN and THEN you can put essentially everything that you can
            put in a WHERE clause.


            --
            felix

            Comment

            • Dark Rayden

              #7
              Re: [MySQL] ORDER BY with fixed values

              On Tue, 19 Apr 2005 13:22:49 GMT, "Felix Geerinckx"
              <felix.geerinck x@gmail.com> wrote:
              [color=blue]
              >On 19/04/2005, Dark Rayden wrote:
              >[color=green]
              >> How would you create those CASE statements?
              >> I mean, if the order should be lik A, B, C and the output is like A,
              >> C, B, how would I construct a statement to make sure, all of the cases
              >> are in the query in the order I expect it?[/color]
              >
              >If we go back to the original example:
              >
              >ORDER BY CASE foo
              > WHEN 'A' THEN 1
              > WHEN 'B' THEN 2
              > WHEN 'B b' THEN 3
              > WHEN 'C' THEN 4
              > WHEN 'D d' THEN 5
              > WHEN 'E' THEN 6
              > ELSE 999
              >END
              >
              >It's the integers 1, 2, ... that are important: when foo = 'A' you get
              >1, when foo = 'D d' you get 5. Since 1 < 5, 'A' will come before 'D d'
              >when you ORDER.[/color]

              Ah, I think I can follow you. Just to make sure here is my 'original'
              code to see, if I got you:

              My old query was:
              mysql_query("SE LECT DISTINCT wein_art FROM $table WHERE kategorie =
              '$kat_array[$bart]' ORDER BY $wein_row") or die ("ERROR while 'SELECT'
              all sub_kategorie from db! ->".mysql_error( ));

              So I have to chage it to:
              mysql_query("SE LECT DISTINCT wein_art FROM $table WHERE kategorie =
              '$kat_array[$bart]' ORDER BY CASE $wein_row
              WHEN 'Welschrieling' THEN 1
              WHEN 'Grüner Veltliner' THEN 2
              WHEN 'Riesling' THEN 3
              and-so-on..
              END
              ") or die ("ERROR while 'SELECT' all sub_kategorie from db!
              ->".mysql_error( ));

              Is this correct?
              [color=blue]
              >Also note that there is another form of CASE, where you can say e.g.
              >
              >-- Order alphabetically, but G and J first
              >ORDER BY CASE
              > WHEN foo LIKE 'G%' THEN 1
              > WHEN foo LIKE 'J%' THEN 2
              > ELSE 999
              >END
              >
              >Between WHEN and THEN you can put essentially everything that you can
              >put in a WHERE clause.[/color]

              Ah, the LIKE option. I never used that before. &)

              Right, I will try this.
              Many thanks for now!!

              Best regards,
              Rayden

              Comment

              • Felix Geerinckx

                #8
                Re: [MySQL] ORDER BY with fixed values

                On 19/04/2005, Dark Rayden wrote:
                [color=blue]
                > So I have to chage it to:
                > mysql_query("SE LECT DISTINCT wein_art FROM $table WHERE kategorie =
                > '$kat_array[$bart]' ORDER BY CASE $wein_row
                > WHEN 'Welschrieling' THEN 1
                > WHEN 'Grüner Veltliner' THEN 2
                > WHEN 'Riesling' THEN 3
                > and-so-on..
                > END
                > ") or die ("ERROR while 'SELECT' all sub_kategorie from db!
                > ->".mysql_error( ));
                >
                > Is this correct?[/color]

                Your $wein_row variable should contain the *name* of the column
                containing the wines, but since this is fixed, you probably don't need
                a variable there but just the column name.

                --
                felix

                Comment

                • Dark Rayden

                  #9
                  Re: [MySQL] ORDER BY with fixed values

                  On Tue, 19 Apr 2005 14:15:50 GMT, "Felix Geerinckx"
                  <felix.geerinck x@gmail.com> wrote:
                  [color=blue]
                  >On 19/04/2005, Dark Rayden wrote:
                  >[color=green]
                  >> So I have to chage it to:
                  >> mysql_query("SE LECT DISTINCT wein_art FROM $table WHERE kategorie =
                  >> '$kat_array[$bart]' ORDER BY CASE $wein_row
                  >> WHEN 'Welschrieling' THEN 1
                  >> WHEN 'Grüner Veltliner' THEN 2
                  >> WHEN 'Riesling' THEN 3
                  >> and-so-on..
                  >> END
                  >> ") or die ("ERROR while 'SELECT' all sub_kategorie from db!
                  >> ->".mysql_error( ));
                  >>
                  >> Is this correct?[/color]
                  >
                  >Your $wein_row variable should contain the *name* of the column
                  >containing the wines, but since this is fixed, you probably don't need
                  >a variable there but just the column name.[/color]

                  All right, my query now looks like this:
                  $sub_cat = mysql_query("SE LECT DISTINCT
                  wein_art FROM $table WHERE kategorie = '$kat_array[$bart]' ORDER BY
                  CASE
                  WHEN 'Welschriesling ' THEN 1
                  WHEN 'Grüner Veltliner' THEN 2
                  WHEN 'Sauvignon blanc' THEN 3
                  WHEN 'Weißburgunder' THEN 4
                  WHEN 'Chardonnay' THEN 5
                  WHEN 'Muskat Ottnonel' THEN 6
                  WHEN 'Gelber Muskatteller' THEN 7
                  WHEN 'Cuvée von Weißweinen' THEN 8
                  WHEN 'Pannobile weiß' THEN 9
                  WHEN 'Exquisites weiß' THEN 10
                  END") or die ("ERROR while 'SELECT'
                  all sub_kategorie from db! ->".mysql_error( ));

                  The order is still wrong like before. If I use the table name instead
                  of the variable (or the variable itself), I get a syntax error from
                  the mysql error.

                  What did I miss?

                  Regards,
                  Rayden

                  Comment

                  • Dark Rayden

                    #10
                    Re: [MySQL] ORDER BY with fixed values

                    On Tue, 19 Apr 2005 16:30:29 +0200, Dark Rayden <rufus_64@gmx.n et>
                    wrote:
                    [color=blue]
                    >On Tue, 19 Apr 2005 14:15:50 GMT, "Felix Geerinckx"
                    ><felix.geerinc kx@gmail.com> wrote:
                    >[color=green]
                    >>On 19/04/2005, Dark Rayden wrote:
                    >>[color=darkred]
                    >>> So I have to chage it to:
                    >>> mysql_query("SE LECT DISTINCT wein_art FROM $table WHERE kategorie =
                    >>> '$kat_array[$bart]' ORDER BY CASE $wein_row
                    >>> WHEN 'Welschrieling' THEN 1
                    >>> WHEN 'Grüner Veltliner' THEN 2
                    >>> WHEN 'Riesling' THEN 3
                    >>> and-so-on..
                    >>> END
                    >>> ") or die ("ERROR while 'SELECT' all sub_kategorie from db!
                    >>> ->".mysql_error( ));
                    >>>
                    >>> Is this correct?[/color]
                    >>
                    >>Your $wein_row variable should contain the *name* of the column
                    >>containing the wines, but since this is fixed, you probably don't need
                    >>a variable there but just the column name.[/color]
                    >
                    >All right, my query now looks like this:
                    > $sub_cat = mysql_query("SE LECT DISTINCT
                    >wein_art FROM $table WHERE kategorie = '$kat_array[$bart]' ORDER BY
                    >CASE
                    > WHEN 'Welschriesling ' THEN 1
                    > WHEN 'Grüner Veltliner' THEN 2
                    > WHEN 'Sauvignon blanc' THEN 3
                    > WHEN 'Weißburgunder' THEN 4
                    > WHEN 'Chardonnay' THEN 5
                    > WHEN 'Muskat Ottnonel' THEN 6
                    > WHEN 'Gelber Muskatteller' THEN 7
                    > WHEN 'Cuvée von Weißweinen' THEN 8
                    > WHEN 'Pannobile weiß' THEN 9
                    > WHEN 'Exquisites weiß' THEN 10
                    > END") or die ("ERROR while 'SELECT'
                    >all sub_kategorie from db! ->".mysql_error( ));
                    >
                    >The order is still wrong like before. If I use the table name instead
                    >of the variable (or the variable itself), I get a syntax error from
                    >the mysql error.
                    >
                    >What did I miss?[/color]
                    A mistake by me! &)

                    Sorry for my silliness. It works correctly as you said. I just had two
                    entries typed wrong, so the came up first ... &)

                    Thank you very much, Felix!
                    You helped me a lot. Now I have to memorize this whole problem for the
                    next time :D

                    Best regards,
                    Rayden

                    Comment

                    • Felix Geerinckx

                      #11
                      Re: [MySQL] ORDER BY with fixed values

                      On 19/04/2005, Dark Rayden wrote:
                      [color=blue][color=green]
                      > > $sub_cat = mysql_query("SE LECT DISTINCT
                      > > wein_art FROM $table WHERE kategorie = '$kat_array[$bart]' ORDER BY
                      > > CASE
                      > > WHEN 'Welschriesling ' THEN 1
                      > > WHEN 'Grüner Veltliner' THEN 2
                      > > WHEN 'Sauvignon blanc' THEN 3
                      > > WHEN 'Weißburgunder' THEN 4
                      > > WHEN 'Chardonnay' THEN 5
                      > > WHEN 'Muskat Ottnonel' THEN 6
                      > > WHEN 'Gelber Muskatteller' THEN 7
                      > > WHEN 'Cuvée von Weißweinen' THEN 8
                      > > WHEN 'Pannobile weiß' THEN 9
                      > > WHEN 'Exquisites weiß' THEN 10
                      > > END") or die ("ERROR while 'SELECT'
                      > > all sub_kategorie from db! ->".mysql_error( ));[/color][/color]
                      [color=blue]
                      > Sorry for my silliness. It works correctly as you said. I just had two
                      > entries typed wrong, so the came up first ... &)[/color]

                      Just one additional remark: it's always good to include an ELSE 999, to
                      catch everything not covered by one of the WHEN ... THEN ..., so these
                      are ORDERed *after* the others. Otherwise, these "leftovers" get NULL
                      values and are ORDERed *in front of* everything else.

                      --
                      felix

                      Comment

                      • Dark Rayden

                        #12
                        Re: [MySQL] ORDER BY with fixed values

                        On Tue, 19 Apr 2005 20:16:20 GMT, "Felix Geerinckx"
                        <felix.geerinck x@gmail.com> wrote:
                        [color=blue]
                        >On 19/04/2005, Dark Rayden wrote:
                        >[color=green][color=darkred]
                        >> > $sub_cat = mysql_query("SE LECT DISTINCT
                        >> > wein_art FROM $table WHERE kategorie = '$kat_array[$bart]' ORDER BY
                        >> > CASE
                        >> > WHEN 'Welschriesling ' THEN 1
                        >> > WHEN 'Grüner Veltliner' THEN 2
                        >> > WHEN 'Sauvignon blanc' THEN 3
                        >> > WHEN 'Weißburgunder' THEN 4
                        >> > WHEN 'Chardonnay' THEN 5
                        >> > WHEN 'Muskat Ottnonel' THEN 6
                        >> > WHEN 'Gelber Muskatteller' THEN 7
                        >> > WHEN 'Cuvée von Weißweinen' THEN 8
                        >> > WHEN 'Pannobile weiß' THEN 9
                        >> > WHEN 'Exquisites weiß' THEN 10
                        >> > END") or die ("ERROR while 'SELECT'
                        >> > all sub_kategorie from db! ->".mysql_error( ));[/color][/color]
                        >[color=green]
                        >> Sorry for my silliness. It works correctly as you said. I just had two
                        >> entries typed wrong, so the came up first ... &)[/color]
                        >
                        >Just one additional remark: it's always good to include an ELSE 999, to
                        >catch everything not covered by one of the WHEN ... THEN ..., so these
                        >are ORDERed *after* the others. Otherwise, these "leftovers" get NULL
                        >values and are ORDERed *in front of* everything else.[/color]

                        Yes, this is absolutely correct. But after all, one can use this to
                        see, if all entrys are correct. So if some entry appears in front of
                        the others (like I had experienced), there must be some mistake. So I
                        guess it's a pretty good "error checking" ... ;)

                        Thanks again!

                        Best regards,
                        Rayden

                        Comment

                        • Bill Karwin

                          #13
                          Re: [MySQL] ORDER BY with fixed values

                          Hi, Felix's solution works well, but I thought I'd offer another
                          alternative.

                          Create a lookup table with the wine names and their expected sort ordering:

                          CREATE TABLE wein_order (
                          wein_art VARCHAR(64) NOT NULL,
                          order INTEGER NOT NULL DEFAULT 0
                          );
                          INSERT INTO wine_order VALUES
                          ('Welschrieslin g', 1),
                          ('Grüner Veltliner', 2),
                          ('Sauvignon blanc', 3),
                          ('Weißburgunder ', 4),
                          ('Chardonnay', 5),
                          ('Muskat Ottnonel', 6),
                          ('Gelber Muskatteller', 7),
                          ('Cuvée von Weißweinen', 8),
                          ('Pannobile weiß', 9),
                          ('Exquisites weiß', 10);

                          Now your query is as follows:

                          SELECT DISTINCT t.wein_art
                          FROM $table AS t LEFT OUTER JOIN wein_order AS o
                          ON t.wein_art = o.wein_art
                          WHERE t.kategorie = '$kat_array[$bart]'
                          ORDER BY COALESCE(o.orde r, 999), t.wein_art

                          The COALESCE function uses a value of 999 in case o.order is NULL, e.g.
                          if no row was matched in the wein_order table. Then in cases of a tie,
                          the sort order of the tied rows is alphabetical.

                          If you get a new wein, just add it to the wein_order table and adjust
                          the integers for sort order. That way won't have to rewrite your
                          queries when you get a new wein.

                          Regards,
                          Bill K.

                          Comment

                          • Felix Geerinckx

                            #14
                            Re: [MySQL] ORDER BY with fixed values

                            On 21/04/2005, Bill Karwin wrote:
                            [color=blue]
                            > Hi, Felix's solution works well, but I thought I'd offer another
                            > alternative.[/color]

                            And a more general and powerful one I admit.
                            [color=blue]
                            > If you get a new wein, just add it to the wein_order table and adjust
                            > the integers for sort order. That way won't have to rewrite your
                            > queries when you get a new wein.[/color]

                            And if you start numbering 100, 200, 300, ... instead of 1, 2, 3, ...
                            you just have to insert new wines with the right integer, without the
                            need to change the others.

                            --
                            felix

                            Comment

                            Working...