SQL Server、MySQL、PostgreSQL、Oracleでn番目に大きい値を取得する

SQLでn番目に大きい値を取得する

MAX()関数とサブクエリ

この方法は、まずMAX()関数を使って最大値を取得し、その値をサブクエリで除外することで、n番目に大きい値を取得します。

SELECT MAX(column_name)
FROM table_name
WHERE column_name < (
  SELECT MAX(column_name)
  FROM table_name
);

例:

SELECT MAX(score)
FROM students
WHERE score < (
  SELECT MAX(score)
  FROM students
);

このクエリは、studentsテーブルのscore列の2番目に大きい値を取得します。

ROW_NUMBER()関数は、各行に順位を割り当てる関数です。この関数を使って、n番目に大きい値を取得することができます。

SELECT column_name
FROM (
  SELECT ROW_NUMBER() OVER (ORDER BY column_name DESC) AS rn, column_name
  FROM table_name
) AS t
WHERE rn = n;

SELECT score
FROM (
  SELECT ROW_NUMBER() OVER (ORDER BY score DESC) AS rn, score
  FROM students
) AS t
WHERE rn = 2;

PERCENTILE_CONT()関数は、指定されたパーセンタイルの値を取得する関数です。この関数を使って、n番目に大きい値を取得することができます。

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY column_name DESC)
FROM table_name;

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY score DESC)
FROM students;

ウィンドウ関数を使うと、より複雑な方法でn番目に大きい値を取得することができます。

SELECT column_name
FROM (
  SELECT column_name,
    SUM(CASE WHEN column_name > LAG(column_name) OVER (ORDER BY column_name DESC) THEN 1 ELSE 0 END) AS rn
  FROM table_name
) AS t
WHERE rn = n;

SELECT score
FROM (
  SELECT score,
    SUM(CASE WHEN score > LAG(score) OVER (ORDER BY score DESC) THEN 1 ELSE 0 END) AS rn
  FROM students
) AS t
WHERE rn = 2;

上記のように、SQLでn番目に大きい値を取得するには、いくつかの方法があります。それぞれ的方法にはメリットとデメリットがあるので、状況に合わせて最適な方法を選択する必要があります。

SQL Server:

SELECT MAX(salary)
FROM employees
WHERE salary < (
  SELECT MAX(salary)
  FROM employees
);

MySQL:

SELECT salary
FROM (
  SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn, salary
  FROM employees
) AS t
WHERE rn = 2;

PostgreSQL:

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary DESC)
FROM employees;

Oracle:

SELECT salary
FROM (
  SELECT salary,
    SUM(CASE WHEN salary > LAG(salary) OVER (ORDER BY salary DESC) THEN 1 ELSE 0 END) AS rn
  FROM employees
) AS t
WHERE rn = 2;

実行結果:

100000

上記以外にも、n番目に大きい値を取得する方法はいくつかあります。詳細は、各データベースのドキュメントを参照してください。

SQLでn番目に大きい値を取得するその他の方法

SELECT TOP 1 column_name
FROM table_name
ORDER BY column_name DESC;

SELECT TOP 1 score
FROM students
ORDER BY score DESC;

SELECT column_name
FROM table_name
ORDER BY column_name DESC
LIMIT 1 OFFSET n - 1;

SELECT score
FROM students
ORDER BY score DESC
LIMIT 1 OFFSET 1;

仮想テーブルを使って、n番目に大きい値を取得することができます。

CREATE TABLE temp_table AS
SELECT column_name,
  ROW_NUMBER() OVER (ORDER BY column_name DESC) AS rn
FROM table_name;

SELECT column_name
FROM temp_table
WHERE rn = n;

CREATE TABLE temp_table AS
SELECT score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS rn
FROM students;

SELECT score
FROM temp_table
WHERE rn = 2;