Hi all:

This may just be a MySQL vs MSSQL difference but I was curious.

I have a dynamic select menu. In MSSQL when you do an ORDER BY any record beginning with an apostrophe appears in alphabetical order as if the apostrophe wasn't there. But I find in MySQL, records that begin with an apostrophe appear at the top of the list.

Maybe I am just used to it, but to me the MSSQL approach is more natural way to look at the list. Is there a way to set the order as it is on MSSQL for MySQL?

Thanks!

    Square1 wrote:

    Maybe I am just used to it, but to me the MSSQL approach is more natural way to look at the list.

    It's more "natural" to you to arbitrarily ignore symbols at the beginning of a string until you find one that you like and order by that one instead?

    Square1 wrote:

    Is there a way to set the order as it is on MSSQL for MySQL?

    Don't order by the string itself, order by the string that you actually want it ordered by (e.g. one where leading apostrophes are stripped off).

      bradgrafelman wrote:

      It's more "natural" to you to arbitrarily ignore symbols at the beginning of a string until you find one that you like and order by that one instead?

      Depends: should (500) Days of Summer sort before or after 300?

      I had a glance at MySQL's documentation just now and it does say something there about setting collation rules (including support for custom collation rules) but for several reasons (one of them being that I don't have an installation of MySQL) I haven't looked any further into it.

        Weedpacket;10990141 wrote:

        Depends: should i Days of Summer[/i] sort before or after 300?

        Perfect example.

        I am sorting athlete names, many of which have nicknames. To me, even though 'Hacksaw' Jim Duggan starts with an apostrophe, my natural instinct is to start searching under "H".

        So for the above answer of using a different sort string, do you mean adding an additional DB column of all names duplicated without apostrophes? That seems like a waste of space.

          Square1;10990145 wrote:

          I am sorting athlete names, many of which have nicknames. To me, even though 'Hacksaw' Jim Duggan starts with an apostrophe, my natural instinct is to start searching under "H".

          Well, unless this is specifically dealt with by language rules for whatever language/country you're dealing with, I suppose this might differ for anyone viewing it. Sure, it still might, but if there are rules, follow them. Personally, I'd never start a name by a nickname. I'd rather write it as Jim "Hacksaw" Duggan. And I would expect to find him sorted under either J or D, depending on wether first or last name is presented first.

          Square1;10990145 wrote:

          So for the above answer of using a different sort string, do you mean adding an additional DB column of all names duplicated without apostrophes? That seems like a waste of space.

          Well, deal with it while ordering then.

          ORDER BY
  CASE WHEN LEFT(name, 1) = ''''
    THEN  SUBSTRING(name, 2)
    ELSE name
  END

            Bless you johanafm. Thank you for all your help.

            And maybe putting the nickname in the middle is the best approach.

              a month later

              I tried to take this ORDER BY using CASE a step further with the following:

              ORDER BY
	eventDate,
	state,
        CASE
        	WHEN LEFT(Events.player, 1) = ''''
        THEN
        	SUBSTRING(Events.player, 2)
        ELSE
       		Events.player
        END,
        Events.location

              The results appear, but they are still in the order of apostrophe first. What am I missing?

              Thank you

                I've no idea since it seems like it should work. Have you tried inspecting your data?

                SELECT LEFT(Events.player, 1), LEFT(Events.player, 1) = ''''
FROM ...
                  14 days later

                  Yep, coding error. It works fine. Thank you!

                    Write a Reply...