# SQL 解題練習
###### tags: `SQL`
Copyright 2021, [月下麒麟](https://hackmd.io/@YMont/note-catalog)

---

## Question
3個學生a,b,c，每個人有4次測驗成績都不一樣0~100分，總共12筆記錄。
使用1個SQL查詢，取出每位學生最好的2筆成績。

## Source Code

SELECT *
FROM 
(   SELECT * FROM member 
    WHERE name='Edgar'
    ORDER BY grade 
    DESC LIMIT 2
)
 UNION
    SELECT *
    FROM 
(
    SELECT * FROM member 
    WHERE name='John' 
    ORDER BY grade 
    DESC LIMIT 2
)
 UNION
SELECT *
FROM 
(
    SELECT * FROM member 
    WHERE name='Mary' 
    ORDER BY grade 
    DESC LIMIT 2
);

(如上為SQLite版本)
因在SQLite上必須要利用多個select來搭配union，否則語法會錯誤。
reference:[Combine two statements with LIMITS using UNION](https://stackoverflow.com/questions/10812910/combine-two-statements-with-limits-using-union)

(   SELECT * FROM member
    WHERE name='Edgar'
    ORDER BY grade
    DESC LIMIT 2)
UNION
(   SELECT * FROM member
    WHERE name='John'
    ORDER BY grade
    DESC LIMIT 2)
UNION
(   SELECT * FROM member
    WHERE name='Mary'
    ORDER BY grade
    DESC LIMIT 2);

(如上為MySQL版本)