Order By in Union

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

LVL 8
CamilliaAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
pcelbaCommented:
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
 
pcelbaCommented:
Of course, the union in above select does not make sense.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
pcelbaCommented:
Not UNION but ORDER BY does not make sense, of course. Sorry.
0
 
CamilliaAuthor Commented:
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
 
CamilliaAuthor Commented:
I think you're right. I posted a wrong statement. Let me post back.
0
 
pcelbaCommented:
Now I understand.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.