Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mysql Order by last name when full name for column

I have the following:

 SELECT * FROM users LEFT JOIN user_info ON users.id=user_info.user_id 
                WHERE 
                    ((user_info.tester != 1) OR (user_info.tester is null)) AND 
                    id in (SELECT explicituser_id FROM user_login WHERE (created < '2012-12-17' OR created >= date_add('2012-12-17', interval 1 day))) AND 
                    id IN (SELECT participte_id FROM roster WHERE roster_id IN (6)) 
                order by 
                    substring_index(users.name, ' ', -1)

I'm simply trying to sort by the users' last name.

However, while it can sort by the first name, the last name is buggy. If the user has quotes around their name (ie. "Abigail Martinez" it will make the sorting incorrect. If the user provides only one name, and it's a nickname (ie. Juan), then it will also make it incorrect. And then there's middle initials (ie. Tiffany S Villa or Steve de la Makinov). Unfortunately, this uses only one column for the full name (users.name).

Any help is appreciated. Thanks!

like image 289
Nina Avatar asked Dec 17 '12 17:12

Nina


People also ask

How do I sort by last name in SQL?

To do this, we must simply type: SELECT * FROM employees ORDER BY first_name, last_name ASC; By writing this “, last_name”, we designated the second column of interest. Now all people with the same first name are ordered by their surname.

Does MySQL column order matter?

Yes, column order does matter.

How do I arrange a column in ascending order in MySQL?

The MySQL ORDER BY Keyword The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.


1 Answers

substring_index(TRIM(users.name), ' ', -1) Adding TRIM will remove trailing spaces. After that, sorting occurs as expected.

like image 123
Nina Avatar answered Oct 17 '22 08:10

Nina