Tags
Advanced SQL Interview Questions and Answers, Advanced SQL tutorial pdf, Buy SQL Server Interview Questions Book Online at Low Price, Complex SQL Challenges, complex sql statement(puzzle), Complex TSQL Challenge, Divide rows into two columns, Download SQL Questions, Download SQL Server Interview Question in PDF, Download SQL SERVER Interview questions, Download SQL Server Interview questions and answers, download sql server interview questions and answers pdf, download sql server interview questions by Pawan Khowal, download sql server interview questions by Pawan Kumar, download sql server interview questions by Pawan Kumar Khowal, Download T-SQL Interview Questions, find records not ending with s, find records not ending with s puzzle, Free Download SQL SERVER Interview questions, implementation and administration, Interesting Interview Questions, Interview Puzzles in SQL Server, Interview Qs.SQL SERVER Questions, Interview questions and Answers for MS SQL Server designing, Interview Questions and Answers For SQL, Interview questions on Joins, Interview Questions on SQL, Interview SQL Puzzles, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Khowal, last non null puzzle, Learn complex SQL, Learn SQL, Learn T-SQL, Microsoft SQL Server interview questions for DBA, MS SQL Server interview questions, MSBI Interview Questions, Objective Puzzle, Pawan, Pawan Khowal, Pawan Kumar, Pawan Kumar Khowal, PL/SQL Challenges, Prev Value puzzle, Previous value puzzle, Puzzle SQL, puzzle sql developer, Puzzle SQl Server, Puzzles, PUzzles in SQL SERVER, Queries for SQL Interview, Records not ending with a character Puzzle, Records not ending with s Puzzle, SELECT Puzzle, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL FAQ, SQL FAQs, SQL Interview Q & A, SQL Interview Questions, SQL Interview Questions - Part 2, SQL Interview Questions for SQL Professionals, SQL Joins, SQL pl/sql puzzles, SQL puzzle, SQL Puzzles, SQL Queries, SQL Queries asked in interviews, SQL Questions, SQL Quiz, SQL Server - Common Interview Questions and Answers, SQL Server - General Interview Questions and Answers, sql server 2008 interview questions, SQL Server 2008 Interview Questions and Answers, SQL Server Database, SQL Server database developer interview questions and answers, sql server dba interview questions, SQL Server developer Interview questions and answers, SQL Server developer Interview questions with answers, SQL Server Developer T-SQL Interview Questions, SQL server filter index with LIKE and RIGHT function?, SQL SERVER Indexes, SQL Server Interview Puzzle, SQL SERVER Interview questions, SQL SERVER Interview questions & Answers, SQL Server Interview Questions - Part 1, SQL Server Interview questions and answers, SQL Server Interview Questions and Answers - Free PDF, SQL SERVER Interview questions and answers for experienced, sql server interview questions and answers for net developers, SQL Server Interview Questions And Answers.pdf, sql server interview questions by Pawan Khowal sql interview questions, SQL SERVER Interview questions pdf, SQL Server Interview Questions | MSBISKILLS.Com, SQL Server Interview Questions | MSBISKILLS.com Top 50 SQL Server Questions & Answers, SQL Server Interview Questions/Answers Part-1, SQL Server Puzzle, SQL SERVER Puzzles, SQL server Questions and Answers, SQL SERVER Tips, SQL Skills, SQL Sudoku, SQL Tips & Tricks, SQL Tips and Tricks, SQL Top clause, SQL Tricks, SQL Trikcy question, sql/database interview for puzzle sql developer, SQLBI Interview Questions, SQLSERVER, T SQL Puzzles, T-SQL Challenge, T-SQL Interview Questions | SQL Server Interviews and Jobs, T-SQL Puzzle, T-SQL Query | [ Replace 6 Consecutive Digits with x from a string Puzzle ], T-SQL Query | [ The Complex Week Puzzle ], T-SQL Server Interview Questions, T-SQL Tricky Puzzles, The Biggest Gap Puzzle, The Gap Puzzle, The Gap Puzzle Puzzle, The Tree Puzzle, Top 10 Frequently Asked SQL Query Interview Questions, TOP 100 SQL SERVER INTERVIEW QUESTIONS QUERIES, Top 50 SQL Server Interview Question for Testers, TOP Clause, Tough SQL Challenges, Tough SQL Puzzles, Tricky Questions, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Puzzle, TSQL Puzzles, TSQL Queries, Week puzzle, What You Can (and Can't) Do With Filtered Indexes
SQL Server – Second Highest Salary Puzzle – Different Methods & the Best Method for Performance
Friends, today I am going to talk about one of the old things. Different methods to find out 2nd highest salary from the employee salary table. I get this question most of the time while facing technical interviews. Although most of us know solution to this problem but most of us didn’t know the best method to achieve it. In this post I am going to talk about the best method in terms of the performance. So let’s first create a sample table and insert some rows into it.
-- CREATE TABLE EmployeeMaxSalary ( EmpId INT PRIMARY KEY ,EmpName VARCHAR(1) ,EmpSalary INT ) GO INSERT INTO EmployeeMaxSalary SELECT DISTINCT Number , CHAR(Number) , Number * 10 / 2 FROM MASTER..SPT_VALUES WHERE number > 0 AND number < 100 CREATE NONCLUSTERED INDEX Ix_EmpSalary ON EmployeeMaxSalary(EmpSalary) INCLUDE ( EmpName ) -- |
METHOD 1 | Using Aggregate Function
-- --METHOD 1 | Using Aggregate Function SELECT EmpId, EmpName, EmpSalary FROM EmployeeMaxSalary WHERE EmpSalary = ( SELECT Max(EmpSalary) FROM EmployeeMaxSalary WHERE EmpSalary < ( SELECT Max(EmpSalary) FROM EmployeeMaxSalary ) ) -- |
METHOD 2 | Using TOP WITH TIES
-- --METHOD 2 | Using TOP WITH TIES SELECT TOP 1 WITH TIES EmpId , EmpName , EmpSalary FROM ( SELECT TOP 2 WITH TIES EmpId , EmpSalary ,EmpName FROM EmployeeMaxSalary ORDER BY EmpSalary DESC ) a ORDER BY EmpSalary -- |
METHOD 3 | Using Ranking Functions
-- --METHOD 3 | Using Ranking Functions SELECT EmpId, EmpName, EmpSalary FROM ( SELECT EmpId, EmpName, EmpSalary , ROW_NUMBER() OVER (ORDER BY EmpSalary DESC) rnk from EmployeeMaxSalary ) X WHERE rnk = 2 -- |
METHOD 4 | Using Correlated Query
-- --METHOD 4 | Using Correlated Query SELECT EmpId, EmpName, EmpSalary FROM EmployeeMaxSalary E1 WHERE 1 = ( SELECT DISTINCT COUNT(*) FROM EmployeeMaxSalary E2 WHERE E2.EmpSalary > E1.EmpSalary ) -- |
METHOD 5 | Using OffSet (SQL 2012 Onwards)
-- --METHOD 5 | Using OffSet SELECT EmpId, EmpName, EmpSalary FROM EmployeeMaxSalary ORDER BY EmpSalary DESC OFFSET (1) ROWS FETCH NEXT 1 ROWS ONLY; -- |
Execution Plan Analysis for all the methods given above-
Now I know that my stats are updated so we can trust the cost coming out in the execution plans. Clearly METHOD 5 – Using OffSet & METHOD 3 – Using Ranking Function are the winners. My personal favorite is method 5. Please note that method 5 is available from SQL 2012 onwards;
That’s all folks; I hope you’ve enjoyed the article and I’ll see you soon with some more articles.
Thanks!
Pawan Kumar Khowal
MSBISKills.com
You must be logged in to post a comment.