SQL subqueries errors

2019-09-08 02:45发布

问题:

Using the AdventureWorks2012 database, I have to change the query below to select the top 10% orders with CustomerId greater than 500 and sequence the orders by TotalDue in descending sequence.

 SELECT *
 FROM Sales.SalesOrderHeader h
 INNER JOIN Sales.SalesOrderDetail d 
 ON d.SalesOrderId = h.SalesOrderId

Here are two of my attempts at solving the problem, but both contain errors:

  Msg 156, Level 15, State 1, Line 9
  Incorrect syntax near the keyword 'DESC'.
  Msg 156, Level 15, State 1, Line 12
  Incorrect syntax near the keyword 'FROM'.

Attempt 1

 SELECT *
 FROM SalesOrderHeader 
 WHERE SalesOrderID IN
 (SELECT TOP 10 PERCENT SalesOrderID 
 FROM SalesOrderDetail 
 WHERE SalesOrderID > 500
 ORDER BY TotalDue DESC);

What am I doing wrong? Thanks.


UPDATE: I removed the comma from the ORDER BY section and managed to execute the query from my first attempt. However, I get an seemingly endless "Executing Successfully" load. In other words, I'm getting over 3000 results and counting when I modified the query below:

    SELECT *
    FROM Sales.SalesOrderHeader 
    WHERE SalesOrderID IN
(SELECT TOP 10 PERCENT SalesOrderID 
FROM Sales.SalesOrderDetail 
WHERE CustomerID > 500
ORDER BY TotalDue DESC);