[RESOLVED] Sort order with an apostrophe

Square1

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!

bradgrafelman

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).

Weedpacket

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.

Square1

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.

johanafm

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

Square1

Bless you johanafm. Thank you for all your help.

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

Square1

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

johanafm

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 ...

Square1

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