04-19-2004, 05:48 AM | #1 (permalink) |
Pure Chewing Satisfaction
Location: can i use bbcode [i]here[/i]?
|
[SQL] ORDER BY question
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.
__________________
Greetings and salutations. Last edited by Moskie; 04-19-2004 at 05:58 AM.. |
04-19-2004, 11:19 AM | #3 (permalink) |
Rookie
Location: Oxford, UK
|
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)).
__________________
I can't understand why people are frightened of new ideas. I'm frightened of the old ones. -- John Cage (1912 - 1992) |
04-19-2004, 11:31 AM | #4 (permalink) |
Location: Waterloo, Ontario
|
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? |
04-19-2004, 01:02 PM | #5 (permalink) |
Pure Chewing Satisfaction
Location: can i use bbcode [i]here[/i]?
|
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!
__________________
Greetings and salutations. |
04-20-2004, 05:18 AM | #7 (permalink) |
Pure Chewing Satisfaction
Location: can i use bbcode [i]here[/i]?
|
My solution was:
Code:
SELECT Name FROM Employee ORDER BY MID(Name,INSTR(Name, ' ')+1) 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.
__________________
Greetings and salutations. |
Tags |
order, question, sql |
|
|