CODEWITHSUNDEEP
mysqlsql
sgx
sgx

Reputation: 1317

Ordering a List by Last Name

If you had a list of names in a table named 'directory' as shown below, how could you write a query that returns these names but displays them in alphabetical order by last name

directory:

Jason Howard, Tom Albert, Sam Smith

To do this by first name, I would think you can just do:

select * from directory order by name

I'm assuming the full name is the field 'name'

Upvotes: 1

Views: 141

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

This is really a note. There is no reason to select the last name in order to order by it. You can just use the expression in the ORDER BY:

ORDER BY SUBSTRING_INDEX(name, ' ', -1)

TRIM() should not be necessary unless your data is really sloppy -- with spaces at the end of the name column.

Upvotes: 1

Hamed Ghasempour
Hamed Ghasempour

Reputation: 445

As jim horn said, it's not a good approach. But if we consider the last part of the name as the last name of users (which of course, separated by spaces), then you can use this query:

SELECT SUBSTRING_INDEX(TRIM(name), ' ', -1) AS LAST_NAME FROM `table_name` ORDER BY LAST_NAME

Upvotes: 1

Jim Horn
Jim Horn

Reputation: 889

Does your table only have the full name with firstname space lastname, or separate columns for first and last name?

If yes, then it's your query with ORDER BY lastname.

If no, then what you're asking is going to be a real pain to code for those with multiple spaces in their name, as you'll never get the logic down for which one is the last name, such as Jean Claude Van Damme, Phil Vander Beek, Hong Kong Phooey, and Bill S. Preston Esquire III.

Upvotes: 0

Related Questions