sort list by lastname then first

I am trying to sort a list generated by wordpress but a bit more custom.

    global $wpdb;
    $orderby = " SUBSTRING_INDEX( {$wpdb->posts}.post_title, ' ', -1 ) ASC"

In wordpress what this is doing is returning that sql statement into an sorting function. This works to sort by the last word of the post title, (last name in my case) but if 2 people have the same last name I’d like it to then sort by first name as well.

So what I need is a sql line that will sort by last name and then First name.

Unfortunately it’s all stored in the same column (post_title) and I can’t change that.

I’m thinking I can somehow sort the column first by first name and then by last name but I am not able to figure it out. Is there a way to do this?

Jill Doodles
Brian Doodle
Billy Graham
Ryan Toon
Zedna Toon
James Zebra
Adam Zebra
David Zebra
Billy Zebra

But I would like it be sorted like this:

Brian Doodles
Jill Doodles
Billy Graham
Ryan Toon
Zedna Toon
Adam Zebra
Billy Zebra
David Zebra
James Zebra

PER @JoakimDanielson, I tried this:

 $orderby = " SUBSTR({$wpdb->posts}.post_title, 1, LOCATE(' ', {$wpdb->posts}.post_title) - 1), SUBSTR({$wpdb->posts}.post_title, LOCATE(' ', {$wpdb->posts}.post_title) + 1) ";

But it only sorted by first name.

Adam Zebra
Billy Graham
Billy Zebra
Brian Doodles
david Zebra
James Zebra
Jill Doodles
Ryan Toon
Zedna Toon

but reversing his code to this made the world a better place:

$orderby = " SUBSTR({$wpdb->posts}.post_title, LOCATE(' ', {$wpdb->posts}.post_title) + 1) ASC, SUBSTR({$wpdb->posts}.post_title, 1, LOCATE(' ', {$wpdb->posts}.post_title) - 1) ASC ";

Answer

If there is only one space, i.e one first name and one surname you can do like this

SELECT name
FROM table
ORDER BY SUBSTR(name, 1, LOCATE(' ', name) - 1),  
         SUBSTR(name, LOCATE(' ', name) + 1)

Above works with 5.6 of MySql but if you have v 8 and a more complex name string you can use regular expressions.

Advertisement

Answer