How to find 3rd highest salary — SQL Technical Interview Questions & Answers

How to find 3rd highest salary

How to find 3rd highest salary

  • 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 find 3rd highest salary: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

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

Code Examples for How to find 3rd highest salary

How to find 3rd highest salary — sample query

-- How to find 3rd highest salary
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

How to find 3rd highest salary: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

How to Explain How to find 3rd highest salary to an Interviewer

3rd highest salary pattern?

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