sorting a column alphabetically

Adam H-W · November 11th, 2003, 08:27 AM

Hi there

I've got a column with peoples names in it - I need to sort them by alphabetical order of the surname. Can anyone help me?

thanks

Adam

nbryson · November 11th, 2003, 08:30 AM

Hi

Use ORDER BY <column name>

See BOL for more info such as DESC.

Nickie

Adam H-W · November 11th, 2003, 08:47 AM

Hi Nickie,

Not sure I explained the question correctly. Both the christian name and the surname are in the same column.

thanks

Adam

chrislepingwell · November 11th, 2003, 08:51 AM

You'll need something like this as the ORDER BY

Code:

substring(NameColumn,charindex(' ',NameColumn)+1,len(NameColumn))

And next time, design your database with separate columns for First and Surnames :)

HTH

Chris

There are two secrets to success in this world:
1. Never tell everything you know

melvik · November 11th, 2003, 08:51 AM

if u ask me u'd better to change ur Table's structure & Normalize it FIRST Then many ways r for ordering.

Always:),
Hovik Melkomian.

nbryson · November 11th, 2003, 08:56 AM

Hi

I was going to give you some code but Chris beat me to it. And yes it would be much easier if you had 2 columns firstname and surname.

Nickie

pgtips · November 11th, 2003, 08:58 AM

Hi Adam,

You could do a lot of fiddling around with things like CHARINDEX to extract the surname, but you'd be better off in the long run spending your time reorganising your data so that its stored properly with the surname in its own column. If you're worried about the impact of such changes on existing processing you could always put a view over the top of your reorganised data that recombines the (now) separate name fields. You could even give that view the same name as your existing table so all current stored procs etc would still work. (What I mean is that you would create a new table with the name separated into multiple fields, then migrate the existing data into this new table, then delete the existing table, then create a view that looks exactly the same as your existing table).

hth
Phil

Adam H-W · November 11th, 2003, 09:12 AM

Thanks guys for all your help - sorted it! unfortunately I had no say over the design of the database and certainly would have taken your comments on board had I done so!

nbryson · November 11th, 2003, 09:29 AM

Hi

I forgot to mention in the last post that you should watch out for the likes of double barreled names and names with suffixes/prefixes etc. I live in the UK and most names are 1 word for firstname and 1 word for second name but foreign names can be varied, and having all this in 1 column gives you problems such as yours.

You should consider this in your sort and having separate columns for such names would be much easier to use, but I am sure you are aware of this.

Nickie