SQL · Theory

How to select nth Highest Record

Theory

323/439

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.

Diagram

  FROM students
        │
        ▼
  WHERE city = 'Pune'
        │
        ▼
  SELECT name, marks

Exam tip

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

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.

Short notes

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

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

How to select nth Highest Record — sample query

Short notes

Questions

How to select nth Highest Record

How to select nth Highest Record — sample query

Short notes

Questions