SQL Server - Need to gt back data based on nested rank

Question

We have a feature that allows us to create SQL to get back data from one table based on a nested query that filters records based on matching criteria from another table. Now, we need to be able to get back data from the first table based on the top x records of the nexted query, rather than all matching records. For Example we want something like

SELECT Name, Address, City, State, Zip 
  FROM CUSTOMERS
 WHERE Customer_Location IN (SELECT TOP 100 
                                    CustomerID, 
                                    Rank() OVER PARTITION BY TID ORDER BY TerritoryName DESC) AS 'RANK'
                               FROM Territories
                              WHERE Nation = 'Canada')

But using the IN statement, so far, is no good because we can only return one column, and using the EXISTS isn't working as all the exists does is return "TRUE" if any fields match (even when we put a link back to the main query). Does anyone know how I can get this to work? Thanks.

Answer 1

You want to join to your nested query. I've made an assumption on the appropriate join condition, but it would be something like:

SELECT Name, Address, City, State, Zip 
FROM CUSTOMERS C
INNER JOIN (SELECT TOP 100 CustomerID, TerritoryName, 
            Rank() OVER PARTITION BY TID ORDER BY TerritoryName DESC) AS 'RANK'
            FROM Territories
            WHERE Nation = 'Canada') T
ON C.Customer_Location = T.TerritoryName

Answer 2

I'm not sure about the join condition either. It seems unexpected to match on Customer_Location and CustomerID. Maybe you can clarify?

I think you would need a CTE or derived table.

WITH R AS
(
SELECT CustomerID, 
Rank() OVER (PARTITION BY TID ORDER BY TerritoryName DESC) AS [rank]
FROM Territories
WHERE Nation = 'Canada'
)


SELECT Name, Address, City, State, Zip 
  FROM CUSTOMERS
 WHERE Customer_Location IN (SELECT CustomerID FROM R WHERE [rank] <= 100)