Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Order By in Union

Posted on 2009-05-18
7
Medium Priority
?
180 Views
Last Modified: 2012-05-07
I want to add a Order By "O.CreatedOn Desc" to this SQL stored procedure but I keep getting this error:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

--How can I do this??
select   'Pending',count(*)
from
(
SELECT  
  
O.[Id] AS [Id],
O.[CreatedOn] AS [CreatedOn],
O.[Number] AS [Number],
O.[CustomerName] AS [CustomerName],
O.[BillingPhoneNumber] AS [BillingPhoneNumber], 
O.[Status] AS [Status],  
COALESCE(OHST.[Name], OHS.[Name]) AS [HoldStatus], 
O.[Score] AS [Score], 
SUM(CASE WHEN OI.[Status] = 1 THEN 0 
         ELSE OI.[ItemPrice] - OI.[DiscountAmount] END) 
        - MAX(O.[OrderDiscountAmount]) +  MAX(O.[TaxAmount]) + MAX(O.[ShippingPrice] - O.[ShippingDiscountAmount]) 
        AS [GrandTotal], 
isnull(Uc.[UserName],'') AS [CreatedBy]  
FROM [ORDOrder] O WITH (NOLOCK) 
    LEFT OUTER JOIN [aspnet_Users] Uc WITH (NOLOCK) ON Uc.[UserId] = O.[CreatedBy]  
    INNER JOIN [ORDOrderItem] OI WITH (NOLOCK) ON OI.[OrderId] = O.[Id] 
    LEFT OUTER JOIN [ORDOrderHoldStatus] OHS WITH (NOLOCK) ON OHS.[Id] = O.[HoldStatusId] 
    LEFT OUTER JOIN [ORDOrderHoldStatusText] OHST WITH (NOLOCK) ON OHST.[Id] = OHS.[Id] AND OHST.[LocaleId] = 1033 
WHERE O.[Status] = 2
      And O.SiteID = @SiteID
 
GROUP BY COALESCE(OHST.[Name], OHS.[Name]),
         O.[BillingPhoneNumber], 
         O.[CreatedOn], 
         O.[CustomerName], 
         O.[Id], 
         O.[Number], 
         O.[Score], 
         O.[Status], 
         Uc.[UserName] 
 
) a
 
Union
select  ',10',count(*)
from
(
SELECT
  
O.[Id] AS [Id],
O.[CreatedOn] AS [CreatedOn],
O.[Number] AS [Number],
O.[CustomerName] AS [CustomerName],
O.[BillingPhoneNumber] AS [BillingPhoneNumber], 
O.[Status] AS [Status],  
COALESCE(OHST.[Name], OHS.[Name]) AS [HoldStatus], 
O.[Score] AS [Score], 
SUM(CASE WHEN OI.[Status] = 1 THEN 0 
         ELSE OI.[ItemPrice] - OI.[DiscountAmount] END) 
        - MAX(O.[OrderDiscountAmount]) +  MAX(O.[TaxAmount]) + MAX(O.[ShippingPrice] - O.[ShippingDiscountAmount]) 
        AS [GrandTotal], 
isnull(Uc.[UserName],'') AS [CreatedBy]  
FROM [ORDOrder] O WITH (NOLOCK) 
    LEFT OUTER JOIN [aspnet_Users] Uc WITH (NOLOCK) ON Uc.[UserId] = O.[CreatedBy]  
    INNER JOIN [ORDOrderItem] OI WITH (NOLOCK) ON OI.[OrderId] = O.[Id] 
    LEFT OUTER JOIN [ORDOrderHoldStatus] OHS WITH (NOLOCK) ON OHS.[Id] = O.[HoldStatusId] 
    LEFT OUTER JOIN [ORDOrderHoldStatusText] OHST WITH (NOLOCK) ON OHST.[Id] = OHS.[Id] AND OHST.[LocaleId] = 1033 
WHERE O.[Status] = 2 and O.[Score] < 10
       And O.SiteID = @SiteID
 
GROUP BY COALESCE(OHST.[Name], OHS.[Name]),
         O.[BillingPhoneNumber], 
         O.[CreatedOn], 
         O.[CustomerName], 
         O.[Id], 
         O.[Number], 
         O.[Score], 
         O.[Status], 
         Uc.[UserName] 
 
) a

Open in new window

0
Comment
Question by:Camillia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 24417100
can you explain why you need an order by in your query?
because, all it does in the end is to return 2 rows for the 2 counts ...
0
 
LVL 43

Expert Comment

by:pcelba
ID: 24417138
Union ALL could help
select   'Pending',count(*)
from
(
SELECT   
O.[Id] AS [Id],
O.[CreatedOn] AS [CreatedOn],
O.[Number] AS [Number],
O.[CustomerName] AS [CustomerName],
O.[BillingPhoneNumber] AS [BillingPhoneNumber], 
O.[Status] AS [Status],  
COALESCE(OHST.[Name], OHS.[Name]) AS [HoldStatus], 
O.[Score] AS [Score], 
SUM(CASE WHEN OI.[Status] = 1 THEN 0 
         ELSE OI.[ItemPrice] - OI.[DiscountAmount] END) 
        - MAX(O.[OrderDiscountAmount]) +  MAX(O.[TaxAmount]) + MAX(O.[ShippingPrice] - O.[ShippingDiscountAmount]) 
        AS [GrandTotal], 
isnull(Uc.[UserName],'') AS [CreatedBy]  
FROM [ORDOrder] O WITH (NOLOCK) 
    LEFT OUTER JOIN [aspnet_Users] Uc WITH (NOLOCK) ON Uc.[UserId] = O.[CreatedBy]  
    INNER JOIN [ORDOrderItem] OI WITH (NOLOCK) ON OI.[OrderId] = O.[Id] 
    LEFT OUTER JOIN [ORDOrderHoldStatus] OHS WITH (NOLOCK) ON OHS.[Id] = O.[HoldStatusId] 
    LEFT OUTER JOIN [ORDOrderHoldStatusText] OHST WITH (NOLOCK) ON OHST.[Id] = OHS.[Id] AND OHST.[LocaleId] = 1033 
WHERE O.[Status] = 2
      And O.SiteID = @SiteID
 GROUP BY COALESCE(OHST.[Name], OHS.[Name]),
         O.[BillingPhoneNumber], 
         O.[CreatedOn], 
         O.[CustomerName], 
         O.[Id], 
         O.[Number], 
         O.[Score], 
         O.[Status], 
         Uc.[UserName] 
 ORDER BY O.CreatedOn Desc
) a 
Union All
select  ',10',count(*)
from
(
SELECT
O.[Id] AS [Id],
O.[CreatedOn] AS [CreatedOn],
O.[Number] AS [Number],
O.[CustomerName] AS [CustomerName],
O.[BillingPhoneNumber] AS [BillingPhoneNumber], 
O.[Status] AS [Status],  
COALESCE(OHST.[Name], OHS.[Name]) AS [HoldStatus], 
O.[Score] AS [Score], 
SUM(CASE WHEN OI.[Status] = 1 THEN 0 
         ELSE OI.[ItemPrice] - OI.[DiscountAmount] END) 
        - MAX(O.[OrderDiscountAmount]) +  MAX(O.[TaxAmount]) + MAX(O.[ShippingPrice] - O.[ShippingDiscountAmount]) 
        AS [GrandTotal], 
isnull(Uc.[UserName],'') AS [CreatedBy]  
FROM [ORDOrder] O WITH (NOLOCK) 
    LEFT OUTER JOIN [aspnet_Users] Uc WITH (NOLOCK) ON Uc.[UserId] = O.[CreatedBy]  
    INNER JOIN [ORDOrderItem] OI WITH (NOLOCK) ON OI.[OrderId] = O.[Id] 
    LEFT OUTER JOIN [ORDOrderHoldStatus] OHS WITH (NOLOCK) ON OHS.[Id] = O.[HoldStatusId] 
    LEFT OUTER JOIN [ORDOrderHoldStatusText] OHST WITH (NOLOCK) ON OHST.[Id] = OHS.[Id] AND OHST.[LocaleId] = 1033 
WHERE O.[Status] = 2 and O.[Score] < 10
       And O.SiteID = @SiteID
 GROUP BY COALESCE(OHST.[Name], OHS.[Name]),
         O.[BillingPhoneNumber], 
         O.[CreatedOn], 
         O.[CustomerName], 
         O.[Id], 
         O.[Number], 
         O.[Score], 
         O.[Status], 
         Uc.[UserName] 
 ORDER BY O.CreatedOn Desc
) b

Open in new window

0
 
LVL 43

Expert Comment

by:pcelba
ID: 24417147
Of course, the union in above select does not make sense.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 43

Expert Comment

by:pcelba
ID: 24417151
Not UNION but ORDER BY does not make sense, of course. Sorry.
0
 
LVL 7

Author Comment

by:Camillia
ID: 24417279
I want to bring back rows by order of date. Oldest date first. Why doesnt it make sense??
Let me try the UNion ALL and see. Maybe it doesnt make sense but let me see..
0
 
LVL 7

Author Comment

by:Camillia
ID: 24417287
I think you're right. I posted a wrong statement. Let me post back.
0
 
LVL 43

Expert Comment

by:pcelba
ID: 24417460
Now I understand.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question