[SOLVED] Help with ORDER BY

[JSHINER]

I have the following code:

 

$query = "SELECT * FROM table WHERE var = '$var' ORDER BY name";

 

Which returns results as:

 

Andrew Smith

Bob Jones

Chris Michaels

 

However, I would like it to order by LAST name - so I would like it to be:

 

Bob Jones

Chris Michaels

Andrew Smith

 

I have made the mistake of storing first and last in one field. So I would like to avoid going through and changing everything so there is a field for first name and last name.

 

I was thinking there may be a way to take what is after the last space and have that be $lastname and then ORDER BY '$lastname'

 

Is this possible?

[trq]

Can't get to my server to test, but something like...

 

$query = "SELECT name, SUBSTRING_INDEX(name, ' ', -1) AS lastname FROM `table` WHERE var = '$var' ORDER BY lastname";

 

should go pretty close.

[JSHINER]

Worked great. Thank you.