ORDER BY Question

I have a table which contains a sNAME field - a persons name of "firstame sp
ace
lastname".
I have a query that delivers a recordset using "ORDER BY sName"
Now, a client has asked to see the data sorted by "lastname". Is there any
method using an SQL expression to deliver that recordset sorted by lastname?
..or do I have to re-organize my data fields and data?
I was hoping for a quick 'sql function' solution instead of changing the
database.
BrianBrian,
You could use functions to determine the lastname and sort by that but that
technique would probably preclude the use of NC indexes to resolve the query
and may negatively impact performance. Something like (or some variation
of):
SELECT <COLUMN LIST.
FROM <TABLE>
ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN> )+ 1,
DATALENGTH(<COLUMN> ) - CHARINDEX(' ',<COLUMN> )) DESC
HTH
Jerry
"Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local...
>I have a table which contains a sNAME field - a persons name of "firstame
>space
> lastname".
> I have a query that delivers a recordset using "ORDER BY sName"
> Now, a client has asked to see the data sorted by "lastname". Is there any
> method using an SQL expression to deliver that recordset sorted by
> lastname?
> ..or do I have to re-organize my data fields and data?
> I was hoping for a quick 'sql function' solution instead of changing the
> database.
> Brian
>|||Jerry,
Thanks...I think that will do for now. The table only has a max of 400 rows
in it.
Brian
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u155VK20FHA.3000@.TK2MSFTNGP12.phx.gbl...
> Brian,
> You could use functions to determine the lastname and sort by that but
that
> technique would probably preclude the use of NC indexes to resolve the
query
> and may negatively impact performance. Something like (or some variation
> of):
> SELECT <COLUMN LIST.
> FROM <TABLE>
> ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN> )+ 1,
> DATALENGTH(<COLUMN> ) - CHARINDEX(' ',<COLUMN> )) DESC
> HTH
> Jerry
>
> "Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
> news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local...
any[vbcol=seagreen]
>|||Jerry,
It worked and it was quick too...thanks again
Brian
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u155VK20FHA.3000@.TK2MSFTNGP12.phx.gbl...
> Brian,
> You could use functions to determine the lastname and sort by that but
> that technique would probably preclude the use of NC indexes to resolve
> the query and may negatively impact performance. Something like (or some
> variation of):
> SELECT <COLUMN LIST.
> FROM <TABLE>
> ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN> )+ 1,
> DATALENGTH(<COLUMN> ) - CHARINDEX(' ',<COLUMN> )) DESC
> HTH
> Jerry
>
> "Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
> news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local...
>sql