Solved

Order By in Union

Posted on 2009-05-18
7
177 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 500 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 42

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 42

Expert Comment

by:pcelba
ID: 24417147
Of course, the union in above select does not make sense.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 42

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 42

Expert Comment

by:pcelba
ID: 24417460
Now I understand.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL order by with "over" statement and row_number() 11 63
Unable to save view in SSMS 21 83
kill process lock Sql server 9 79
Need to find substring in SQL 5 52
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

737 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