Geek Answers Handbook

SQL Server 2012 Using EXCEPT with ORDER BY

I can accomplish this.

SELECT TOP 10 model, price
FROM PC 
EXCEPT
SELECT TOP 9 model, price
FROM PC ORDER BY price DESC

But when I run this, I got a syntax error next to "Order".

SELECT TOP 10 model, price
FROM PC ORDER BY price DESC
EXCEPT
SELECT TOP 9 model, price
FROM PC ORDER BY price DESC

And I have to do it instead. Why is this not working?

SELECT * FROM
(
SELECT TOP 10 model, price
FROM PC ORDER BY price DESC
EXCEPT
SELECT TOP 9 model, price
FROM PC ORDER BY price DESC
) X
+4
source share
1 answer

Explanation

A proposal is ORDER BYallowed in two situations: when there is TOP(or OFFSET/FETCH) and in the outermost query a series of queries / subqueries that work together as a single query. Now you can’t use a ORDER BYquery in the internal context without a suggestion TOP, as you know. But additionally, when two situations ( TOPcompared to external ones) conflict, the most external request context takes precedence.

UNION, EXCEPT INTERSECT, , ORDER BY, , . , , - , .

EXCEPT ed , , ORDER BY.

SQL Server 2012 , OFFSET FETCH :

SELECT model, price
FROM dbo.PC
ORDER BY price DESC
OFFSET 9 ROWS 
FETCH NEXT 1 ROWS ONLY;

, , , , . , :

  • ( , )
  • Fast

, -, , ( ) .

+5

More articles:


All Articles