ROW_NUMBER() — SQL Technical Interview Questions & Answers

ROW_NUMBER()

ROW_NUMBER()

  • 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: ROW_NUMBER(): 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 ROW_NUMBER()

ROW_NUMBER() — sample query

-- ROW_NUMBER()
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;

ROW_NUMBER(): create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

How to Explain ROW_NUMBER() to an Interviewer

3rd highest salary pattern?

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