NTH_VALUE

1
SELECT name, score,
2
  NTH_VALUE(score, 2) OVER (
3
    ORDER BY score DESC
4
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
5
  ) AS second_highest
6
FROM students;

1
SELECT name, salary,
2
  NTH_VALUE(salary, 3) OVER (
3
    ORDER BY salary
4
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
5
  ) AS third_salary
6
FROM top5_employees;

1
SELECT category, product, sales,
2
  NTH_VALUE(product, 1) OVER w AS gold,
3
  NTH_VALUE(product, 2) OVER w AS silver,
4
  NTH_VALUE(product, 3) OVER w AS bronze
5
FROM products
6
WINDOW w AS (
7
  PARTITION BY category 
8
  ORDER BY sales DESC
9
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
10
);