"Order By items must appear in Select List" error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

"Order By items must appear in Select List" error

Author

Topic

ThreePea
Yak Posting Veteran

83 Posts

Posted - 2002-03-08 : 11:48:33

The following piece of code...


USE Northwind

SELECT 0,'Name',0

UNION

SELECT
  P.ProductID,
  P.ProductName,
  (SELECT TOP 1 OD.OrderID
   FROM [Order Details] AS OD (NOLOCK)
   WHERE OD.ProductID = P.ProductID
   ORDER BY OD.Quantity DESC) AS 'OrderID_WITH_HIGH_QTY'
FROM Products AS P (NOLOCK)

generates the following error:

Server: Msg 104, Level 15, State 1, Line 3
ORDER BY items must appear in the select list if the statement contains a UNION operator.

Why does the compiler care that the ORDER BY is present, since it is inside a scalar subquery? I could insert the results of both SELECT statements into a temp table, and then just return the temp table, but I was trying to avoid that overhead. Any ideas?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-08 : 11:56:26

UNION already does an ORDER BY, UNION ALL does not. The other reason is that the UNION statement is effectively one set of results, so technically you really can't order a part of it. I also think that the TOP clause will interfere too.

Try the temp table and measure the performance; the overhead could very well be negligible. I've done untold numbers of temp table processes, and only the ones that exceeded 10,000 rows ever caused me significant performance issues.

Jay99

468 Posts

Posted - 2002-03-08 : 12:08:07

Here's another way to get what your are looking for.


SELECT 0 as productid ,'Name' as productname ,0 as orderid_with_high_qty
UNION
SELECT
    P.ProductID,
    P.ProductName,
    od.OrderID as OrderID_WITH_HIGH_QTY
FROM Products P (nolock)
     inner join [Order Details] OD (nolock)
        on p.productid = od.productid
where
    not exists (
        select 1
        from
            [Order Details]
        where
            productid = od.productid and
            quantity > od.quantity)
order by productid

Note: you original select would disregard ties, where as mine shows orders that tie for the highest qty count. You can modify mine to choose one, though . . .

Jay

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-03-08 : 14:57:21

Here's another:


CREATE VIEW Products_WITH_HIGH_QTY_Orders
AS
SELECT
  P.ProductID,
  P.ProductName,
  (SELECT TOP 1 OD.OrderID
   FROM [Order Details] AS OD (NOLOCK)
   WHERE OD.ProductID = P.ProductID
   ORDER BY OD.Quantity DESC) AS 'OrderID_WITH_HIGH_QTY'
FROM Products AS P (NOLOCK)


GO


SELECT 0,'Name',0
UNION
SELECT * FROM Products_WITH_HIGH_QTY_Orders

Looks pretty fishy to me that the original query didn't work.

What the hell, here's another possibility:


SELECT 0,'Name',0
UNION
SELECT P.ProductID, ProductName, MAX(OD.OrderID) AS 'OrderID_WITH_HIGH_QTY'
FROM Products AS P (NOLOCK)
INNER JOIN [Order Details] OD (NOLOCK) ON P.ProductID = OD.ProductID
WHERE Quantity = (
  SELECT MAX(Quantity)
  FROM [Order Details] (NOLOCK)
  WHERE ProductID = OD.ProductID)
GROUP BY P.ProductID, ProductName

Edited by - Arnold Fribble on 03/08/2002 15:16:16

Jay99

468 Posts

Posted - 2002-03-08 : 15:39:02

Oh yeah? . . .


select 0, 'Name', 0
union
select
    p.productid,
    p.productname,
    (select top 1
        orderid
    from
        [order details] od
    where
        od.productid = p.productid and
        quantity = (select
                        max(quantity)
                    from
                        [order details]
                    where
                        productid = od.productid))
from
    products p

Jay

EDIT: with a subtree cost of 0.464 my initial solution runs faster than any of the others

(and with a subtree cost of 35.2, this last solution is the worst . . . )

Edited by - Jay99 on 03/08/2002 15:42:34

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-03-08 : 15:49:11

Not sure what you mean by "Oh yeah? . . .". Just think it looks like a parser bug that it's picking up the ORDER BY in the subquery as an error when it works perfectly well with that part of the query in a view.

Yes, SQL Server's a bit hit or miss on optimizing TOPs in subqueries: replace the TOP 1 with a MAX and it behaves itself.

Edited by - Arnold Fribble on 03/08/2002 15:50:49

Jay99

468 Posts

Posted - 2002-03-08 : 16:04:08

quote:

Not sure what you mean by "Oh yeah? . . .".

I was just playing the 'how-many-ways-can-we-write-the-query' game . . . friday afternoon and all . . .

quote:

Just think it looks like a parser bug that it's picking up the ORDER BY in the subquery as an error when it works perfectly well with that part of the query in a view.

I'll certainly agree with you here. I/we was/were just offering ThreePea a work around.

quote:

Yes, SQL Server's a bit hit or miss on optimizing TOPs in subqueries: replace the TOP 1 with a MAX and it behaves itself.

This actually gets back to what I said at the end of my original post and how each of these solutions deals with ties on max(quantity).

Jay

ThreePea
Yak Posting Veteran

83 Posts

Posted - 2002-03-08 : 16:46:45

quote:

I'll certainly agree with you here. I/we was/were just offering ThreePea a work around.

Ha, you guys are great. Thanks.

Jerry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources