[SQL] ORDER BY question

[Moskie]

As part of an assignment, I've been asked to sort and display some information using different sort methods. One of the sort methods I'm assigned is to sort the information directly in the SQL statement. That normally would be extremely simply, but it seems particularly complicated in this situation.

I have a table called Employee, which has a column called "Name". Names in this column are in the form "<FirstName><space><LastName>". Such as "Joe Smith". The problem is that I am supposed to sort by Employee's last name, which means I can't just do "ORDER BY Name". Is there some kinky thing I can put in the ORDER BY clause to get this sorted properly?

It also crossed my mind that I could write a stored procedure that would produce a record's last name for me, but I'm not sure how to do that... my string-manipulation-within-SQL skills are not that great, and I'm not even sure if it can be done.

Any thoughts on how to do this?


edit: I should add that I'm doing this with an MBD file in a Visual Studio .Net 2003 VB application.

[Fallon]

Are you restricted to having the name all together? Or can you make a first/last name?
Sorry I got limited knowledge of SQL

[cliche]

Can you not ORDER BY SUBSTRING(Name FROM LOCATE(' ',Name)) or similar? (I'm aware that this includes the space).
I don't have an SQL set up to see if that would work but might be worth a try. Alternatively ORDER BY SUBSTRING(Name, LOCATE(' ',Name)).

[KnifeMissile]

Unfortunately, I don't know SQL well enough to answer your literal question (but cliche looks like he's onto something) but let me ask you an obvious question.

Is there a particular reason why first name and surname don't have their own columns? Usually, this is the case and, in this case, sorting by surname is easy.
Is this difficulty, in fact, part of the assignment?

[Moskie]

Thanks guys, I got a similar idea to cliche's working. Wasn't nearly as bad as I feared.

And I do think that was the purpose of it KnifeMissile... at least I hope so. If he says I could have just split it up into two columns, i'll be pissed!

[Latch]

Just for general knowledge, what'd you end up with (that worked)?

Thanks.

[Moskie]

My solution was:

SELECT Name FROM Employee ORDER BY MID(Name,INSTR(Name, ' ')+1)

INSTR returns the index of the first space in Name. Using that index, I use MID to get everything after that location, which is the last name. ORDER BY that result, and you got it sorted.

I'm not sure how you'd do this on a different (non-MSAccess) database, but I think most databases have equivalent functions to use.

[Cuball]

if you normalized your table you wouldn't have to deal with that kind of problem