a_to_z sort with a - in

[brown2005]

SELECT domains_url, SUBSTRING(domains_url, 1, 1) AS letter
FROM a
INNER JOIN domains ON a_domain=domains_id
ORDER BY letter ASC, domains_url ASC

when I use this it will sort like

 

hello-world.co.uk

hello-zulu.co.uk

hello.co.uk

 

but I am hoping to get the hello first,

 

any ideas please?

[Barand]

try

SELECT domains_url
...
ORDER BY SUBSTRING_INDEX(domains_url,'.',1)

[Psycho]

Huh? Hello exists in all the results, so what do you mean you want "hello" first? I'm guessing (and that's all it is because the question is vague) that you want the "hello.co.uk" to be first because the "hello" is followed by a period. Ok, what about sub-domains when they do and do not exist. E.g.:

 

domain.com

domain.aa.com

domain.zz.com

 

Do you want the "domain.com" to be first because it doesn't have a sub-domain or should the domain.aa.com be first since it is alphabetically first?

 

Assuming you just want to do the first part and nothing else, I see two options:

 

1. Use regular expression in the sort logic to determine the sort order using a replacement character for the '.' (period) that will come before the '-' (hyphen). But RegEx is notoriously inefficient. Depening how much data you have and how much this process is used you could have performance issues. Here is an example that sorts on the domain - but does so after replacing the '.' (periods) with an '!' (exclamation point).

 

SELECT domains_url, SUBSTRING(domains_url, 1, 1) AS letter
FROM a
INNER JOIN domains ON a_domain=domains_id
ORDER BY letter ASC, REPLACE(domains_url, '.', '!') ASC

 

2. A better solution, in my opinion, is to store a new value for your records such as "sort name". Then you can define the business rules and save that second value into the table used only for sorting purposes. For example a title such as "The Adventure" would have a sort name of "Adventure, The"

[Psycho]

 

try

SELECT domains_url
...
ORDER BY SUBSTRING_INDEX(domains_url,'.',1)

 

@Barand, a '1' does not sort before a '.'. You would need to use one of the "shift" characters above the 1 to 0 on the keyboard "!@#$%^&*()". There are others, of course, but those are the ones that I know would work off the top of my head.

[Barand]

@Psycho,

Worked for me

mysql> SELECT * FROM domain;
+----+-------------------+
| id | domain_name       |
+----+-------------------+
|  1 | hello-world.co.uk |
|  2 | hello.co.uk       |
|  3 | hello-zulu.co.uk  |
+----+-------------------+

mysql> SELECT * FROM domain
    -> ORDER BY SUBSTRING_INDEX(domain_name, '.', 1);
+----+-------------------+
| id | domain_name       |
+----+-------------------+
|  2 | hello.co.uk       |
|  1 | hello-world.co.uk |
|  3 | hello-zulu.co.uk  |
+----+-------------------+

[Psycho]

 

@Psycho,

Worked for me

 

My apologies, I didn't see you used SUBSTRING_INDEX() - which is probably more efficient than what I provided. Totally different approach to get the same results . Although, either method may not work based upon how he wants to handle sub-domains.

[brown2005]

I have used Barand's solution and that works perfectly. Thank you very much