I want to find classical Greatest N per Group. I have two methods to solve this problem
Dense_rank Over()methodMin Over()method
Both work flawlessly. Now I want to find which one is better and why.
Sample data:
CREATE TABLE #test
(
id INT,
NAME VARCHAR(50),
dates DATETIME
);
WITH cte
AS (SELECT TOP (100000) n = ( (Row_number() OVER (ORDER BY a.number) - 1) / 3) + 1
FROM [master]..spt_values a
CROSS JOIN [master]..spt_values b)
INSERT INTO #test
SELECT n,
Getdate() + n
FROM cte ;
Dense_rank Over() Method:
WITH cte
AS (SELECT Dense_rank() OVER (partition BY NAME ORDER BY dates) AS rn,*
FROM #test)
SELECT id,
NAME,
dates
FROM cte
WHERE rn = 1;
Min Over() Method:
WITH cte
AS (SELECT Min(dates) OVER (partition BY NAME) AS max_date,*
FROM #test)
SELECT id,
NAME,
dates
FROM cte
WHERE max_date = dates ;
To compare the performance I checked on execution plan which said both query cost is 50%. But Max Over method execution plan looks little complex. So any guidance here will be helpful. I don't have very good understanding in execution plans.
Execution Plan:(6 rows)
Execution plan:(100000 rows)
For 100000 rows the execution plan says
Dense_rank Over() Query cost : 46%
Min Over() Query cost : 54%

