phpmyadmin Question posted in
The official documentation can be found here.

Mysql DISTINCT with more than one column (remove duplicates) – Phpmyadmin

My database is called: (training_session)
I try to print out some information from my data, but I do not want to have any duplicates. I do get it somehow, may someone tell me what I do wrong?

enter image description here

SELECT DISTINCT athlete_id AND duration FROM training_session
SELECT DISTINCT athlete_id, duration FROM training_session

It works perfectly if i use only one column, but when I add another. it does not work.

Tags:

2

Answers


  1. 0

    MySQL’s DISTINCT clause is used to filter out duplicate recordsets.

    If your query was SELECT DISTINCT athlete_id FROM training_session then your output would be:

    athlete_id
----------
1
2
3
4
5
6

    As soon as you add another column to your query (in your example, the column called duration) then each record resulting from your query are unique, hence the results you’re getting. In other words the query is working correctly.

    Login or Signup to reply.
  2. 0

    I think you misunderstood the use of DISTINCT.
    There is big difference between using DISTINCT and GROUP BY.
    Both have some sort of goal, but they have different purpose.

    You use DISTINCT if you want to show a series of columns and never repeat. That means you dont care about calculations or group function aggregates. DISTINCT will show different RESULTS if you keep adding more columns in your SELECT (if the table has many columns)

    You use GROUP BY if you want to show “distinctively” on a certain selected columns and you use group function to calculate the data related to it. Therefore you use GROUP BY if you want to use group functions.
    Please check group functions you can use in this link.
    https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html

    EDIT 1:
    It seems like you are trying to get the “latest” of a certain athlete, I’ll assume the current scenario if there is no ID.

    Here is my alternate solution:

    SELECT a.athlete_id ,
(  SELECT b.duration
    FROM training_session as b
    WHERE b.athlete_id =   a.athlete_id -- connect
     ORDER BY [latest column to sort] DESC
    LIMIT 1 
 ) last_duration
FROM training_session as a
GROUP BY a.athlete_id
ORDER BY a.athlete_id

    This syntax is called IN-SELECT subquery. With the help of LIMIT 1, it shows the topmost record. In-select subquery must have 1 record to return or else it shows error.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search