Say I have a Customer table and a Purchase table, with a one to zero or many relationship between Customer and Purchase.
I understand I can use the rank() function in SQL Server to get, for example, the top 10 customers based on the purchase amount. But I would also like to get a specific customer's rank within the entire system, but without returning the entire dataset of customers ranked ahead of that customer.
I'm a little rusty on SQL (Entity Framework's fault, haha) so I wasn't able to build an efficient statement for this query. Does anybody know if rank() or some other function can do this?
This sounds like a good candidate for a CTE and using a window function like you mention. You can declare the specific customer, use a CTE to get all customer's purchase amount, use a second cte to rank them all, then query the top 10 and union the specific customer info. The union will drop the 11th person if they are already in the top 10. UNION ALL will give you a duplicate record, but provide 11 rows.
DECLARE @userID INT = xxxx
;WITH cte1 AS (
SELECT
customerID
, SUM(purchase_dollars) AS purchase_amount
FROM customer_table
GROUP BY customerID)
, cte_rank AS (
SELECT
customerID
, RANK() OVER(ORDER BY purchase_amount DESC) AS purchase_amount_rank
--, ROW_NUMBER() OVER(ORDER BY purchase_amount DESC) AS purchase_amount_rank --This will get you 10 records
FROM cte1)
SELECT
t1.CustomerID
, t2.purchase_amount_rank
FROM customer_table t1
INNER JOIN cte_rank t2
ON t1.customerid = t2.customerid
WHERE t2.purchase_amount_rank <= 10
UNION
SELECT t1.customerID
, t2.purchase_amount_rank
FROM customer_table t1
INNER JOIN cte_rank t2
ON t1.customerid = t2.customerid
AND t1.customer_id = @userID
ORDER BY t2.purchase_amount_rank