Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin.
Search for scripts directly from SSMS, and instantly access any saved scripts in your
SSC briefcase from the favorites tab.
Download now (direct download link)
Thank this author by sharing:
Now lets insert some records into the table;
INSERT INTO Risk Values('AAA1','BBB1','CCC1',1);
INSERT INTO Risk Values('AAA2','BBB2','CCC2',2);
INSERT INTO Risk Values('AAA3','BBB3','CCC3',3);
INSERT INTO Risk Values('AAA4','BBB4','CCC4',4);
INSERT INTO Risk Values('AAA5','BBB5','CCC5',5);
INSERT INTO Risk Values('AAA6','BBB6','CCC6',6);
INSERT INTO Risk Values('AAA7','BBB7','CCC7',7);
INSERT INTO Risk Values('AAA8','BBB8','CCC8',8);
INSERT INTO Risk Values('AAA9','BBB9','CCC9',9);
INSERT INTO Risk Values('AAA10','BBB10','CCC10',10);
Now the query is;
SELECT Col001,Col002,Col003,RowNum_Risk FROM Risk
WHERE RowNum_Risk = (SELECT MIN(RowNum_Risk) FROM (SELECT DISTINCT TOP 5 RowNum_Risk FROM Risk ORDER BY RowNum_Risk DESC)B)
This query will return 5th Max record from the table. If you wish to find 8th or 10th or any number, just replace 5 with the number you want.
Now Lets look at the execution plan of the query.
MS SQL is using Index Scan for SELECT DISTINCT TOP 5 RowNum_Risk FROM Risk ORDER BY RowNum_Risk DESC and the getting top N record.
It is then using Index Seek for the outer query and producing the result.
This is the best solution I found for selecting Nth Max or Min number from a massive table in MS SQL Server. How ever when you are trying to find say 1000000th Max record from the data set then the query execution time will be more. But it is in minutes and not in hours!
I hope you were able to attend my free webinar on Creating Power Map Reporting Solutions on April 1...
In Datawarehousing, a normal scenario is eleminating duplicate records/rows or deleting duplicate re...
SQL Server High Avilabilty Solutions