Using SELECT TOP from one column, then sorting on a different column

Question

I'm using SQL Server 2005, and I want to query for the vendors generating the most revenue, sorted by the vendor's name. Below is the query I have tried. The inner subquery gets the 15 largest vendors sorted by revenue, and I try to order those results by the vendor name.

SELECT Revenue, VendorName
FROM (
  SELECT TOP 15
         SUM(po.POTotal) AS Revenue
       , Vendors.VendorName AS VendorName
  FROM PurchaseOrders po
  INNER JOIN Vendors ON po.Vendor_ID = Vendors.Vendor_ID
  WHERE ...
  GROUP BY Vendors.VendorName
  ORDER BY Revenue DESC
)
ORDER BY VendorName ASC

But this gives me an error message:

Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'ORDER'.

Is there another way to do this? I think this might be possible with a view, but I'd prefer not to do it that way.

---

^{I apologize if this is a duplicate, I don't even know what to search for to see if this has already been asked.}

Answer 1

Add an alias for the subquery:

SELECT Revenue, VendorName
FROM (SELECT TOP 15         
        SUM(po.POTotal) AS Revenue, 
        v.VendorName AS VendorName  
      FROM PurchaseOrders po  
        JOIN Vendors v 
          ON po.Vendor_ID = v.Vendor_ID 
      WHERE ... 
      GROUP BY v.VendorName  
      ORDER BY Revenue DESC) Z
ORDER BY VendorName ASC

Answer 2

You need to give your derived table an alias:

  ...
  ORDER BY Revenue DESC
) AS DerivedTable
ORDER BY VendorName;

Answer 3

I believe you can do this with a CTE:

WITH revenue (Revenue, VendorName)
AS
(SELECT TOP 15 SUM(po.POTotal) AS Revenue, Vendors.VendorName AS VendorName  
FROM PurchaseOrders po  
INNER JOIN Vendors 
ON po.Vendor_ID = Vendors.Vendor_ID  
WHERE ...  
GROUP BY Vendors.VendorName  
ORDER BY Revenue DESC)
SELECT Revenue, VendorName
FROM revenue
ORDER BY VendorName ASC

Answer 4

You can also do this without a sub-query if you like --

SELECT sum(po.POTotal) as Revenue, vendors.VendorName
FROM   PurchaseOrders po  INNER JOIN Vendors ON po.Vendor_ID = Vendors.Vendor_ID  
WHERE ...  
GROUP BY Vendors.VendorName  
ORDER BY sum(po.POTotal) DESC, VendorName ASC

Try that and see if it works - we do the same sort of thing here and this was our solution...

Answer 5

Sorry, forgot the TOP 15 in the query above - it needs to go just befor the sum() aggregate function.