MySQL: Selecting Earliest Instance of Each Unique Combination of Two Columns

Question

For a table called test_section there are columns topic, section_type, result, test_id (FK).

test_id is the primary key of a table called test, which also has a column called student_id and test_date.

For a given student_id, I want to find every combination of topic and section_type and the result for that combination. When multiple instances of a combionation exist for that student, I want to return the result with a test_id corresponding to the earliest test_date (the initial result).

It can be assumed that each test_id will have a unique test_date and that a test_id will not be linked to more than one instance of any combination of topic and section_type.

What sort of query should I use here? As of now, I can only think of using multiple subqueries, but I ideally want the most efficient approach.

If using version 8.0, look into `row_number()`. You can simulate on older versions but not as pretty. — Isolated, Mar 14 '23 at 18:03
Please run the SQL query `SELECT VERSION();` and report what it shows. — Bill Karwin, Mar 14 '23 at 18:03
Does this answer your question? [Fetch the rows which have the Max value for a column for each distinct value of another column](https://stackoverflow.com/questions/121387/fetch-the-rows-which-have-the-max-value-for-a-column-for-each-distinct-value-of) — philipxy, Mar 14 '23 at 22:05
Clearly this is a faq. Please before considering posting: Pin down code issues via [mre]. Read manuals/references & google error messages & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] Reflect research in posts. — philipxy, Mar 14 '23 at 22:06

score 1 · Accepted Answer · answered Mar 14 '23 at 18:08

Solution for MySQL 8.0+ using a window function:

SELECT topic, section_type, result
FROM (
    SELECT topic, section_type, result, 
      ROW_NUMBER() OVER (PARTITION BY topic, section_type 
        ORDER BY test_date) AS rownum 
    FROM test
    JOIN test_section USING (test_id)
    WHERE student_id = ?
) AS t
WHER rownum = 1;

MySQL: Selecting Earliest Instance of Each Unique Combination of Two Columns

1 Answers1