Error when using UNION and correlated subquery with ORDER BY
-
I am trying to UNION some statements together. One statement uses a correlated subquery that contains an ORDER BY CLAUSE and when I run the query I get the following error in SQL 2000.
Msg 104, Level 15, State 1, Line 1: ORDER BY items must appear in the select list if the statement contains a UNION operator.
As a simple example, the following query works fine in SQL 2005, but not in SQL 2000:
SELECT NULL CompanyName, NULL OrderDate UNION ALL
SELECT c.CompanyName, o.OrderDate
FROM Orders o INNER JOIN Customers c ON o.CustomerId = c.CustomerId
WHERE c.CustomerID = 'VINET' AND o.OrderId IN
(SELECT TOP 3 o2.OrderID
FROM Orders o2
WHERE o2.CustomerID = o.CustomerID
ORDER BY o2.OrderDate DESC)
To get around this right now, I've created a table variable to get the information I need using the correlated subquery. Later I select from the table variable and UNION the results to other SELECT statements I need for my result set.
Is there a way to get the same results without using table variables or a while loop? Or is there a fix for SQL 2000 that somebody knows of that will allow me to execute the query without generating an error (and please don't tell me the easy fix would be to use SQL 2005
, because it's a few months down the road before the db I'm using will be migrated to 2005)Thanks!
-
JOINing a derived table in place of the IN clause should do the trick. Try this (and keep in mind I haven't tested it):
SELECT NULL CompanyName, NULL OrderDate
UNION ALL
SELECT c.CompanyName, o.OrderDate
FROM Orders o
INNER JOIN (SELECT TOP 3 o2.OrderID
FROM Orders
ORDER BY o2.OrderDate DESC
) o2
ON o2.CustomerID = o.CustomerID
INNER JOIN Customers c
ON o.CustomerId = c.CustomerId
WHERE c.CustomerID = 'VINET'
-
John: Don't you still run into the same problem of having an order by clause with an item not in the select statment? This seems to be a reoccuring problem when working with any UNION clause.
Additionally wouldn't you also just get the top three orders without it necessarily being for the customer he is interested in?
Not trying to put you on the spot, I don't visualize well without actully working against data (a big problem I know!).
James.
-
JLK - you are absolutely correct. I apologize for my hasty posting. As I stated, I did not test the code I posted; moreover, I did really read it to see what he was attempting to do.
Ryan, can you post some sample data and an example of the result set you are after? Here’s my best guess on what you are attempting to do (with my own created sample data). Hope this helps:
DECLARE @Orders TABLE (OrderID int IDENTITY(1,1),OrderDate datetime, CustomerID varchar(20))
DECLARE @Customers TABLE (CustomerID varchar(20), CompanyName varchar(20))
SET NOCOUNT ON
INSERT INTO @Customers
SELECT 'VINET', 'VINET' UNION ALL
SELECT 'Test', 'Test Company'
INSERT INTO @Orders (OrderDate, CustomerID)
SELECT '2007-06-02' as OrderDate, 'VINET' UNION ALL
SELECT '2007-06-12' as OrderDate, 'VINET' UNION ALL
SELECT '2007-06-20' as OrderDate, 'VINET' UNION ALL
SELECT '2007-06-21' as OrderDate, 'VINET' UNION ALL
SELECT '2007-06-22' as OrderDate, 'VINET' UNION ALL
SELECT '2007-06-22' as OrderDate, 'Test' UNION ALL
SELECT '2007-06-22' as OrderDate, 'Test'
SELECT *
FROM @Orders
SELECT *
FROM @Customers
SELECT *
FROM @Orders
ORDER BY OrderDate DESC
SELECT NULL CompanyName, NULL OrderDate
UNION ALL
SELECT TOP 3 c.CompanyName, o.OrderDate
FROM @Orders o
INNER JOIN @Customers c
ON o.CustomerId = c.CustomerId
WHERE c.CustomerID = 'VINET'
ORDER BY o.OrderDate DESC
-
John: No problem, just wanted to make sure I wasn't missing something. Also I just checked and Ryan's orginal "example" code appears to run against the northwind database, so if you have that you have some test data.
I don't have a better solution to offer than his original idea of a table variable or possibly a function that holds the second part of the union statement and returns the table data he wants.
Example:
create function dbo.fnTest ()
returns table
as
return (
SELECT c.CompanyName, o.OrderDate
FROM Orders o INNER JOIN Customers c ON o.CustomerId = c.CustomerId
WHERE c.CustomerID = 'VINET' AND o.OrderId IN
(SELECT TOP 3 o2.OrderID
FROM Orders o2
WHERE o2.CustomerID = o.CustomerID
ORDER BY o2.OrderDate DESC))
SELECT NULL CompanyName, NULL OrderDate UNION ALL
select * from dbo.fnTest()
-
Well it appears that he just wants the last 3 orders by date for that specific customer and then wants to be able to UNION that in with another result set. What I've posted in my second post will do that fine, but I have a feeling we're missing part of the picture here.
-
Here are some more details. I need the Top 15 orders for EACH Employee. I have done some testing and the correlated subquery runs much faster than a table variable solution I am currently using. I'll try putting the query into a function and see if that works. Thanks for your help!
DECLARE @Orders TABLE (OrderID int IDENTITY(1,1),OrderDate datetime, CustomerID varchar(20))
DECLARE @Customers TABLE (CustomerID varchar(20), CompanyName varchar(20))
SET NOCOUNT ON
INSERT INTO @Customers
SELECT 'VINET', 'VINET' UNION ALL
SELECT 'Test', 'Test Company' UNION ALL
SELECT 'JONES', 'Jones Inc'
INSERT INTO @Orders (OrderDate, CustomerID)
SELECT '2007-06-02' as OrderDate, 'VINET' UNION ALL
SELECT '2007-06-12' as OrderDate, 'VINET' UNION ALL
SELECT '2007-06-20' as OrderDate, 'VINET' UNION ALL
SELECT '2007-06-18' as OrderDate, 'JONES' UNION ALL
SELECT '2007-06-19' as OrderDate, 'JONES' UNION ALL
SELECT '2007-06-22' as OrderDate, 'JONES' UNION ALL
SELECT '2007-06-14' as OrderDate, 'Test' UNION ALL
SELECT '2007-06-27' as OrderDate, 'Test' UNION ALL
SELECT '2007-06-30' as OrderDate, 'Test'
SELECT *
FROM @Orders
SELECT *
FROM @Customers
/* Give Top 2 For Each CustomerId */
-- Works Fine in SQL 2000 without UNION to other statements
SELECT c.CustomerID, c.CompanyName, o.OrderDate
FROM @Orders o INNER JOIN @Customers c ON o.CustomerId = c.CustomerId
WHERE o.OrderId IN
(SELECT TOP 2 o2.OrderID FROM
@Orders o2 WHERE o2.CustomerID = o.CustomerID
Order By o2.OrderDate DESC)
-- Doesn't work with UNION in SQL 2000
SELECT NULL CustomerID, NULL CompanyName, NULL OrderDate
UNION ALL
SELECT c.CustomerID, c.CompanyName, o.OrderDate
FROM @Orders o INNER JOIN @Customers c ON o.CustomerId = c.CustomerId
WHERE o.OrderId IN
(SELECT TOP 2 o2.OrderID FROM
@Orders o2 WHERE o2.CustomerID = o.CustomerID
Order By o2.OrderDate DESC)
-
Another thing Ryan, correlated sub queries are usually poor performers and can be re-written using JOINs or derived tables. I would be careful with taking any approach involving correlated sub queries if there is an alternative solution. What are the row counts of your employee and orders tables?
-
I have a report that needs different regions of data. Rather than having each region be a seperate dataset I created a query that combines queries for the different regions of data together into one result set. I created a unique groupname for each region. I use NULL values so that I don't have to supply a value for every column of every Select statement.
My actual result set returns 10 different regions and 60 columns, but below is an example of what I am essentially doing in my query:
SELECT 'Customer' GroupType, C.CustomerId, C.CompanyName,
NULL OrderDate, NULL ShippedDate, NULL ProductOrdered, Null Quantity
FROM Customers C
UNION ALL
SELECT 'Orders', O.CustomerId, NULL, O.OrderDate, O.ShippedDate, NULL, NULL
FROM Orders O
UNION ALL
SELECT 'OrderDetails', O.CustomerId, NULL, O.OrderDate, NULL,
P.ProductName, OD.Quantity
FROM ORDERS O INNER JOIN [Order Details] OD
ON O.OrderId = OD.OrderId
INNER JOIN Products P
ON P.ProductId = OD.ProductId
-
CREATE VIEW TOP2PERORDER
AS
SELECT c.CustomerID, c.CompanyName, o.OrderDate, 2 data
FROM Orders o INNER JOIN Customers c ON o.CustomerId = c.CustomerId
WHERE o.OrderId IN
(SELECT TOP 2 o2.OrderID FROM
Orders o2 WHERE o2.CustomerID = o.CustomerID
Order By o2.OrderDate DESC)
Then Try:
SELECT NULL CustomerID, NULL CompanyName, NULL OrderDate, 1 data
UNION ALL
SELECT * FROM TOP2PERORDER
Cheers!
* Noel -
In 2005 your statement works without the View trick
* Noel
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply