Twitterを見ていたら気になったツイートが
次のテーブルの3番目にscoreが高い生徒(students)をSELECTせよ。
ほぅ。。
解いてみた
問題のSQL
CREATE TABLE students (
id INTEGER,
name VARCHAR(128),
score INTEGER
);
INSERT INTO students (id, name, score)
VALUES (1, 'Alice', 85),
(2, 'Bob', 90),
(3, 'Charlie', 88),
(4, 'Mark', 82);
自分が思いついた回答
SELECT score FROM students
ORDER BY score DESC
LIMIT 1 OFFSET 2
TLに書いてあった回答
おしい・・・
①
SELECT * FROM students WHERE score = (
SELECT DISTINCT score FROM students
ORDER BY score DESC
LIMIT 1 OFFSET 2);
②WITH句とDENSE_RANK関数を活用
WITH ranked_students AS (
SELECT * DENSE_RANK() OVER (ORDER BY score DESC ) AS rank FROM students
)
SELECT id, name, score
FROM ranked_students
WHERE rank = 3
LIMIT 1;
WITHはわかるけどDENSE_RANK関数?
調べた感じOracleで使われる関数ぽい
https://segakuin.com/oracle/function/dense_rank.html