Order By Lastname

I have a field called contactname, which looks like this

Code:

---

Al Davis
Bob Smith
Robert Smith
John Anderson
Jake Anderson

---

What I would like to do is somehow order it by the lastname.
Anyone know how I can do this, i have been using substring and charindex, etc...but haven't been able to figure out how to order this field by the last name.

have you been able to return Lastname as a separate column?

if so, you can order by the column (if you return it), or by the formula you would use to create it (but you may need to Select either the formula or the contactname field).

if not let us know, and I'll have a look.

No, i haven't been able to separate them.
The database is SQL Server 2000.

Ideally I would like them to be listed like this

Code:

---

Anderson Jake
Anderson John
Davis Al
Smith Bob
Smith Robert

---

The reason is I need to remove duplicates. So if someone is listed as Bob Smith and Robert Smith. I can spot it fairly quickly.

You mentioned using substring and charindex, can you just not get that to work properly or do you want something else?

Here is a working example of using CharIndex and SubString to order the table by last name (assuming that the first and last name are always seperated by a space):

Code:

---

Select Substring(contact, Charindex(' ', contact)+1, Len(contact) - CharIndex(' ', contact)) +
        ' ' + Substring(contact, 1, Charindex(' ', contact) - 1)
From myTable
Order By Substring(contact, Charindex(' ', contact)+1, Len(contact) - CharIndex(' ', contact)) +
        ' ' + Substring(contact, 1, Charindex(' ', contact) - 1)

---

That's almost identical to how I did it, but I didnt' use substring.

Code:

---

SELECT LTrim(Right(ContactName, CHARINDEX(' ', Reverse(ContactName)))) + ', ' +
        Left(ContactName, CHARINDEX(' ', Reverse(ContactName))) As 'ContactName'
FROM ContactList
Order By 'ContactName'

---

However, I get this error when trying to run your sql statement

Quote:

---

Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.

---

Code:

---

DECLARE  @db_data_tablesA TABLE ( table_id int identity(1,1), my_names varchar(25) )
SET NOCOUNT ON
insert @db_data_tablesA( my_names ) values ('Al Davis')
insert @db_data_tablesA( my_names ) values ('Bob Smith')
insert @db_data_tablesA( my_names ) values ('Robert Smith')
insert @db_data_tablesA( my_names ) values ('John Anderson')
insert @db_data_tablesA( my_names ) values ('Jake Anderson')
insert @db_data_tablesA( my_names ) values ('Randa Styss Anderson')
insert @db_data_tablesA( my_names ) values ('Randa Styss-Anderson')

SELECT *
  FROM @db_data_tablesA 
  LEFT JOIN
  ( SELECT shortName
      FROM
      ( SELECT RIGHT( my_names, CHARINDEX(' ', REVERSE( my_names))- 1 )
          FROM @db_data_tablesA 
      ) AS a( shortName ) GROUP BY shortName HAVING COUNT(*) > 1
  ) AS b( shorty )
  ON shorty = RIGHT( my_names, CHARINDEX(' ', REVERSE( my_names))- 1 )
ORDER BY shorty, my_names

---

HTH

Quote:

---

Originally posted by Memnoch1207
That's almost identical to how I did it, but I didnt' use substring.

Code:

---

SELECT LTrim(Right(ContactName, CHARINDEX(' ', Reverse(ContactName)))) + ', ' +
        Left(ContactName, CHARINDEX(' ', Reverse(ContactName))) As 'ContactName'
FROM ContactList
Order By 'ContactName'

---

However, I get this error when trying to run your sql statement

---

I've tried the T-SQL from vb_dba.... and found no error. Are you sure you copy-paste the same T-SQL ?