Jump to content

a_to_z sort with a - in


brown2005
 Share

Go to solution Solved by Barand,

Recommended Posts

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?

Link to comment
Share on other sites

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"

Link to comment
Share on other sites

 

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.

Link to comment
Share on other sites

@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  |
+----+-------------------+
Link to comment
Share on other sites

 

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

Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.