How to select nth Highest Record — SQL Technical Interview Questions & Answers

How to select nth Highest Record

How to select nth Highest Record

  • ROW_NUMBER() OVER (ORDER BY marks DESC) gives 1,2,3 with no ties sharing a number. RANK() gives 1,1,3 if two toppers. DENSE_RANK() gives 1,1,2.
  • Nth highest: wrap in a subquery WHERE rn = 3. That’s the ‘3rd highest salary’ pattern.
  • Trap: ROW_NUMBER without ORDER BY in OVER — meaningless ranks.
  • On the example next to this theory: How to select nth Highest Record — sELECT projects columns; WHERE filters; ORDER BY sorts.

Technical Interview Tip: ROW_NUMBER() OVER (ORDER BY salary DESC) then WHERE rn = 3.

Code Examples for How to select nth Highest Record

How to select nth Highest Record — sample query

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Eng', 70000),
  (2, 'Ravi', 'Eng', 65000),
  (3, 'Neha', 'HR', 50000);
SELECT name, salary FROM employees WHERE dept = 'Eng' ORDER BY salary DESC;

How to select nth Highest Record — sELECT projects columns; WHERE filters; ORDER BY sorts.

How to Explain How to select nth Highest Record to an Interviewer

3rd highest salary pattern?

ROW_NUMBER() OVER (ORDER BY salary DESC) Filter rn = 3