Can I "ORDER BY" by Surnames(Lastnames) in a Field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Can I "ORDER BY" by Surnames(Lastnames) in a Field

Author

Topic

savvy
Starting Member

48 Posts

Posted - 2007-03-08 : 07:31:28

I got a strange problem of Orderings of names. I stored the Full Names (First Name LastName) of people
in a single Field called 'Member' with their relevant personal details.
Now my client wants me to sort or order the names by their surnames,
I dont actually know the reason when the same can be possible by sorting with their First Names.
I could have simply done like this "SELECT * FROM Members ORDER BY Member" but its not possible now
Anyways, I got my data like this

MemberID Member
1. Andrew J. Thompson
2. David Stallone
3. James Alex
4. Helen Nicole Kilcon
5. John K. Peterson

So, basically I want like this by ordering by their lastnames(surnames)

MemberID Member
3. James Alex
4. Helen Nicole Kilcon
5. John K. Peterson
2. David Stallone
1. Andrew J. Thompson

Is it actually possible?? I have no clue how to do this
Any suggestions will be of great help to me

Thanks in Advance

Savvy

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-08 : 07:39:59

[code]declare @t table (memberid int, member varchar(40))

insert @t
select 1, 'Andrew J. Thompson' union all
select 2, 'David Stallone' union all
select 3, 'James Alex' union all
select 4, 'Helen Nicole Kilcon' union all
select 5, 'John K. Peterson'

select * from @t
order by parsename(replace(member, ' ', '.'), 1)[/code]

Peter Larsson
Helsingborg, Sweden

savvy
Starting Member

48 Posts

Posted - 2007-03-08 : 07:51:46

Thank you very much for your quick reply Peso
I quite didn't understand that, but it actually works, thats really great
May be I am being silly, but I already got a table of Members with 150 members in it already
is it anyway I can select all of them and insert in the @t table,
Something like

declare @t table (memberid int, member varchar(40))

insert @t
(Selet * from Members )
select * from @t order by parsename(replace(member, ' ', '.'), 1)

I tried it but its not working

if that can be possible that will solve my problem, I am really sorry for being fussy or silly
Thanks in Advance

Savvy

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-08 : 07:53:05

You only use this!

select * from {YourTableNameHere}
order by parsename(replace(Member, ' ', '.'), 1)

Peter Larsson
Helsingborg, Sweden

savvy
Starting Member

48 Posts

Posted - 2007-03-08 : 07:58:25

Oh my God!!
That works like a beauty..
Thank you very very much Mr.Peso.
Your help is really answer to my prayers, I am really happy
Thank you very very much mate

Savvy

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-08 : 08:09:52

savvy -- i recommend strongly using peso's code to help you break up the data into separate columns, so you would store Firstname and Lastname in two different columns. Once you have lots of data, sorting in this manner will be inefficient, and also it may not always work. For example, what if someone's last name is "De La Rosa" ? That will be sorted under "R". use a parsing routine to help clean the data, but be sure to store it correctly and manually eyeball it an fix it, and then going forward of course, as new members are added be sure to break the names down into the proper columns.

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-08 : 08:10:14

And if there many people with same lastname, use this

select * from {YourTableNameHere}
order by parsename(replace(Member, ' ', '.'), 1), member

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources