CODEWITHSUNDEEP
mysqlsql-order-by
jlafforgue
jlafforgue

Reputation: 287

Sort results by a field that contains names and surname

I wonder if it is possible in MySQL to order my results via a name column that contains firstname and surnames.

Exemple :

"title" : Albert CATERSON - Barabara ABELS - Chris WASPINK

SQL .. 
ORDER BY title

The result will be:

Albert CATERSON - Barabara ABELS - Chris WASPINK

But I want to sort my results by surname :

ABELS Barabara - CATERSON Albert - WASPINK Chris

Do you know if it is possible to do it in mysql? Thank you.

Upvotes: 0

Views: 45

Answers (2)

Abdul Manaf
Abdul Manaf

Reputation: 4888

Try like this

DROP TABLE IF EXISTS OrderBySurname;
Query OK, 0 rows affected, 1 warning (0.01 sec)


CREATE TABLE OrderBySurname(id INT, title VARCHAR(255));
Query OK, 0 rows affected (0.26 sec)


INSERT INTO `OrderBySurname` (`id`, `title`) VALUES ('1','Albert CATERSON'),('2', 'Barabara ABELS'),('3', 'Chris WASPINK');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0


SELECT title , SUBSTRING_INDEX(title, ' ', -1) AS sur_name FROM OrderBySurname ORDER BY sur_name;
+-----------------+----------+
| title           | sur_name |
+-----------------+----------+
| Barabara ABELS  | ABELS    |
| Albert CATERSON | CATERSON |
| Chris WASPINK   | WASPINK  |
+-----------------+----------+
3 rows in set (0.00 sec)


SELECT CONCAT(SUBSTRING_INDEX(title, ' ', -1),' ',SUBSTRING_INDEX(title, ' ', 1)) FROM OrderBySurname ORDER BY SUBSTRING_INDEX(title, ' ', -1);
+----------------------------------------------------------------------------+
| CONCAT(SUBSTRING_INDEX(title, ' ', -1),' ',SUBSTRING_INDEX(title, ' ', 1)) |
+----------------------------------------------------------------------------+
| ABELS Barabara                                                             |
| CATERSON Albert                                                            |
| WASPINK Chris                                                              |
+----------------------------------------------------------------------------+
3 rows in set (0.00 sec)

Upvotes: 1

Alexander
Alexander

Reputation: 3179

If you want to order by surname, you need to use SUBSTRING_INDEX:

ORDER BY SUBSTRING_INDEX(title, ' ', -1);

However, if you want also surname to appear first, you gonna need something like that:
SELECT CONCAT(SUBSTRING_INDEX(title, ' ', -1), ' ', SUBSTRING_INDEX(title, ' ', 1))

Upvotes: 0

Related Questions