I want to find classical Greatest N per Group. I have two methods to solve this problem
Both work flawlessly. Now I want to find which one is better and why.
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)
100000 rows the execution plan says
Dense_rank Over() Query cost : 46% Min Over() Query cost : 54%