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 ?