ORDER BY Question

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.
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...
> >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,
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...
>>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
>

ORDER BY in UNION

I cannot make the result set ORDER BY LastName and FirstName:
CREATE VIEW dbo.viewApp_Web_ContactDetails
AS
SELECT TOP 100 PERCENT dbo.tblApp_Contact.Salutation,
dbo.tblApp_Contact.FirstName,
dbo.tblApp_Contact.LastName
FROM dbo.tblApp_Contact
ORDER BY tblApp_Contact.LastName, tblApp_Contact.FirstName
UNION ALL
SELECT TOP 100 PERCENT dbo.tblApp_WebContact.Salutation,
dbo.tblApp_WebContact.FirstName,
dbo.tblApp_WebContact.LastName
FROM dbo.tblApp_WebContact
ORDER BY tblApp_WebContact.LastName, tblApp_WebContact.FirstName
How do I make the result set in the order by LastName and then FirstName ?Man Utd (alanpltseNOSPAM@.yahoo.com.au) writes:
> I cannot make the result set ORDER BY LastName and FirstName:
> CREATE VIEW dbo.viewApp_Web_ContactDetails
> AS
> SELECT TOP 100 PERCENT dbo.tblApp_Contact.Salutation,
> dbo.tblApp_Contact.FirstName,
> dbo.tblApp_Contact.LastName
> FROM dbo.tblApp_Contact
> ORDER BY tblApp_Contact.LastName, tblApp_Contact.FirstName
> UNION ALL
> SELECT TOP 100 PERCENT dbo.tblApp_WebContact.Salutation,
> dbo.tblApp_WebContact.FirstName,
> dbo.tblApp_WebContact.LastName
> FROM dbo.tblApp_WebContact
> ORDER BY tblApp_WebContact.LastName, tblApp_WebContact.FirstName
> How do I make the result set in the order by LastName and then FirstName ?
Remove the first ORDER BY clause. ORDER BY applies to the entire SELECT
statement, not the various parts in a UNION.
Then again, it's not really meaningful to have ORDER BY in a view. You
should always use ORDER BY when you select data. If you say "SELECT * FROM
myview", without ORDER BY, you can get data back in any order.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Man,
This might help you
CREATE VIEW dbo.viewApp_Web_ContactDetails
AS
SELECT Salutation, FirstName, LastName
FROM(
SELECT dbo.tblApp_Contact.Salutation,
dbo.tblApp_Contact.FirstName,
dbo.tblApp_Contact.LastName
FROM dbo.tblApp_Contact
UNION ALL
SELECT dbo.tblApp_WebContact.Salutation,
dbo.tblApp_WebContact.FirstName,
dbo.tblApp_WebContact.LastName
FROM dbo.tblApp_WebContact
) TEMP_TAB
ORDER BY LastName, FirstName
Please let me know if you have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Man Utd" wrote:

> I cannot make the result set ORDER BY LastName and FirstName:
> CREATE VIEW dbo.viewApp_Web_ContactDetails
> AS
> SELECT TOP 100 PERCENT dbo.tblApp_Contact.Salutation,
> dbo.tblApp_Contact.FirstName,
> dbo.tblApp_Contact.LastName
> FROM dbo.tblApp_Contact
> ORDER BY tblApp_Contact.LastName, tblApp_Contact.FirstName
> UNION ALL
> SELECT TOP 100 PERCENT dbo.tblApp_WebContact.Salutation,
> dbo.tblApp_WebContact.FirstName,
> dbo.tblApp_WebContact.LastName
> FROM dbo.tblApp_WebContact
> ORDER BY tblApp_WebContact.LastName, tblApp_WebContact.FirstName
> How do I make the result set in the order by LastName and then FirstName ?
>
>|||There's just one thing missing: TOP must be specified in the SELECT statemen
t
of a view if ORDER BY is specified.
Also, the use of a derived table is not really needed.
CREATE VIEW dbo.viewApp_Web_ContactDetails
AS
SELECT TOP 100 PERCENT dbo.tblApp_Contact.Salutation,
dbo.tblApp_Contact.FirstName,
dbo.tblApp_Contact.LastName
FROM dbo.tblApp_Contact
UNION ALL
SELECT TOP 100 PERCENT dbo.tblApp_WebContact.Salutation,
dbo.tblApp_WebContact.FirstName,
dbo.tblApp_WebContact.LastName
FROM dbo.tblApp_WebContact
ORDER BY tblApp_WebContact.LastName, tblApp_WebContact.FirstName
As Erland already mentioned.
ML|||"ML" <ML@.discussions.microsoft.com> wrote in message
news:B8F58855-C6D2-4D7E-B02A-F606A3B1A2E3@.microsoft.com...
> There's just one thing missing: TOP must be specified in the SELECT
> statement
> of a view if ORDER BY is specified.
> SELECT TOP 100 PERCENT
Well I'm not sure about the OP, but it's answered my question.
Thanks|||So, I guess my mother was right - sometimes I answer questions before they
were asked. :)
ML|||In this view definition, the ORDER BY clause has no function, because it
does not change the resultset (since you want to select 100 PERCENT).
And since a view has no implicit order, an ORDER BY clause for sorting
purposes does not work.
If you want a specific order, then you must specify an ORDER BY clause
when you select from the view.
Gert-Jan
Man Utd wrote:
> I cannot make the result set ORDER BY LastName and FirstName:
> CREATE VIEW dbo.viewApp_Web_ContactDetails
> AS
> SELECT TOP 100 PERCENT dbo.tblApp_Contact.Salutation,
> dbo.tblApp_Contact.FirstName,
> dbo.tblApp_Contact.LastName
> FROM dbo.tblApp_Contact
> ORDER BY tblApp_Contact.LastName, tblApp_Contact.FirstName
> UNION ALL
> SELECT TOP 100 PERCENT dbo.tblApp_WebContact.Salutation,
> dbo.tblApp_WebContact.FirstName,
> dbo.tblApp_WebContact.LastName
> FROM dbo.tblApp_WebContact
> ORDER BY tblApp_WebContact.LastName, tblApp_WebContact.FirstName
> How do I make the result set in the order by LastName and then FirstName ?