SQL · Theory

ROW_NUMBER()

Theory

288/439

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.

Exam tip

ROW_NUMBER() OVER (ORDER BY salary DESC) then WHERE rn = 3.

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.

Short notes

DefROW_NUMBER() unique rank per ORDER BY.
RuleNeed OVER (ORDER BY …). Ties get different numbers.
TrapMissing ORDER BY in OVER.

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

ROW_NUMBER() — sample query

Short notes

Questions

ROW_NUMBER()

ROW_NUMBER() — sample query

Short notes

Questions