Hostnames Ordering

Description


Yesterday you wrote down the links for some resources that you are going to use as references in your academic paper, and now you want to sort them. You didn’t bother to write down the entire link for any of them, so all you have is a bunch of unique hostnames.

You stored the information about these hostnames in the table hostnames, which has the structure:

  • id: the unique hostname id;
  • hostname: the unique hostname.

Each hostname contain several domains and can be written in the format hostname = domain1.domain2. .... To sort the hostnames, you’ve decided that hostname A should go before hostname B in the sorted list if its reversed list of domains is lexicographically smaller than the reversed list of B’s domains.

Given the table hostnames, build the resulting table as follows: The table should have columns id and hostname, and its values should be ordered as described above. It is guaranteed that all the hostnames are different and that there are no more than 3 domains in each hostname.

Example

For the following tables hostnames

id hostname
1 workbench.mysql.com
2 codesignal.slack.com
3 codesignal.com
4 snarknews.info
5 sololearn.com
6 dev.mysql.com

the output should be

id hostname
3 codesignal.com
6 dev.mysql.com
1 workbench.mysql.com
2 codesignal.slack.com
5 sololearn.com
4 snarknews.info
  • [execution time limit] 10 seconds (mysql)

Solution


1
2
3
4
5
6
7
8
9
/*Please add ; after each select statement*/
CREATE PROCEDURE hostnamesOrdering()
BEGIN
	SELECT id, hostname
    FROM hostnames
    GROUP BY SUBSTRING_INDEX(CONCAT(' .',hostname), '.', -1),
    SUBSTRING_INDEX(CONCAT(' ..',hostname), '.', -2), 
    hostname ASC;
END