Query to select record ignoring duplicates

Question

I need to return a list of students with the respective task results, however, students can repeat the task, this means that there are duplicates. I want to get the first result but I want to get the first result from all the students in the class.

My current query that returns all the results including duplicates is:

SELECT student_id, attempt_id, task_score
FROM Attempt JOIN SetPaper ON Attempt.paper_id = SetPaper.paper_id
WHERE SetPaper.task_id = {X} AND class_id = {Y} AND task_status = "complete";

Where 'X' and 'Y' are predetermined variables.

This currently will return:

+------------+------------+------------+
| student_id | attempt_id | task_score |
+------------+------------+------------+
|    X0000   |      1     |     70     |
|    X0001   |      2     |     40     |
|    X0001   |      3     |     50     |
+------------+------------+------------+

This is because in this case student 'X0001' has attempted and completed the task twice. However, I want it in the format:

+------------+------------+------------+
| student_id | attempt_id | task_score |
+------------+------------+------------+
|    X0000   |      1     |     70     |
|    X0001   |      2     |     40     |
+------------+------------+------------+

So that it only gets their first attempts results.

I have tried using DISTINCT(student_id) but that still gets the other results if I want to return more that just the student id's.

Thanks,

Ryan.

*Never* use commas in the `FROM` clause. *Always* use proper, explicit `JOIN` syntax. — Gordon Linoff, Mar 11 '17 at 15:03
@Gordon Linoff Sorry, I don't use SQL a lot, could you explain why that is the correct way of formatting? as the way we're shown was to use commas. — cookies, Mar 11 '17 at 15:05

score 1 · Accepted Answer · edited May 23 '17 at 12:32

Quick Answer (TL;DR)

DeveloperAndreaCook wishes to perform a JOIN query that returns distinct results on a table.
This can be done using MySQL GROUP BY

Detailed Answer

Context

MySQL 5.x
Distinct results from a SELECT JOIN Query

Problem

Scenario: Developer wishes to perform a JOIN query that returns distinct results

Solution

use GROUP BY Expression

Sample Data

qqperson
  +----+---------+
  | id | fname   |
  +----+---------+
  |  1 | alice   |
  |  2 | bobby   |
  |  3 | charlie |
  |  4 | danny   |
  |  5 | eddie   |
  |  6 | freddy  |
  +----+---------+

qqtask
  +----+------------+-----------+-------------+
  | id | taskname   | taskscore | qqperson_id |
  +----+------------+-----------+-------------+
  |  1 | action-a   | 40        |           1 |
  |  2 | action-aa  | 50        |           1 |
  |  3 | action-b   | 40        |           2 |
  |  4 | action-c   | 50        |           3 |
  |  5 | action-d   | 50        |           4 |
  |  6 | action-aaa | 60        |           1 |
  +----+------------+-----------+-------------+

Example00

example00 (query) sample query that includes unwanted duplicates

SELECT
  'x' AS `x`
  ,`qqperson`.`id` AS `person_id`
  ,`qqperson`.`fname` AS `fname`
  ,`qqtask`.`qqperson_id` AS `qqperson_id`
  ,`qqtask`.`taskscore` AS `taskscore`
  ,`qqtask`.`id` AS `task_id`
FROM
  (
    `qqperson`
    JOIN `qqtask` ON (
      (
        `qqtask`.`qqperson_id` = `qqperson`.`id`
      )
    )
  )

example00 (result)

x    | person_id | fname   | qqperson_id | taskscore | task_id
x    | 1         | alice   | 1           | 40        | 1      
x    | 1         | alice   | 1           | 50        | 2      
x    | 2         | bobby   | 2           | 40        | 3      
x    | 3         | charlie | 3           | 50        | 4      
x    | 4         | danny   | 4           | 50        | 5      
x    | 1         | alice   | 1           | 60        | 6

Example01

example01 (query) ignore duplicates with ORDER BY and GROUP BY

SELECT
  'x' AS `x`
  ,`qqperson`.`id` AS `person_id`
  ,`qqperson`.`fname` AS `fname`
  ,`qqtask`.`qqperson_id` AS `qqperson_id`
  ,`qqtask`.`taskscore` AS `taskscore`
  ,`qqtask`.`id` AS `task_id`
FROM
  (
    `qqperson`
    JOIN `qqtask` ON (
      (
        `qqtask`.`qqperson_id` = `qqperson`.`id`
      )
    )
  )

GROUP BY
  `qqperson`.`id`

ORDER BY
  `qqperson`.`id`

example01 (result)

x    | person_id | fname   | qqperson_id | taskscore | task_id
x    | 1         | alice   | 1           | 40        | 1      
x    | 2         | bobby   | 2           | 40        | 3      
x    | 3         | charlie | 3           | 50        | 4      
x    | 4         | danny   | 4           | 50        | 5

Pitfalls

The ORDER BY clause will affect the output of the query
sql_mode=only_full_group_by