DISTINCT does not remove duplicates

Question

Can't remove duplicates with SELECT DISTINCT for some reason.

My tables:

mysql> select * from kasutaja;
+----+---------+----------+---------------+
| id | eesnimi | perenimi | kasutaja_nimi |
+----+---------+----------+---------------+
|  1 | Juku    | Juust    | juku23        |
|  2 | Jaan    | Jaanik   | jann12        |
+----+---------+----------+---------------+

mysql> select * from riistvara;
+----+----------------+-----------+
| id | r_nimetus      | seeria_nr |
+----+----------------+-----------+
|  1 | Latitude L2100 |  33333333 |
|  2 | Latitude L2110 |  44444444 |
+----+----------------+-----------+

mysql> select * from r_paigaldus;
+-------------+--------------+----------------+
| kasutaja_id | riistvara_id | paigalduse_aeg |
+-------------+--------------+----------------+
|           1 |            1 | 2010-01-01     |
|           1 |            2 | 2010-10-01     |
|           2 |            2 | 2010-01-01     |
|           2 |            1 | 2010-10-10     |
+-------------+--------------+----------------+

The query I'm using:

SELECT DISTINCT kasutaja_nimi, eesnimi, perenimi, r_nimetus, seeria_nr, paigalduse_aeg 
FROM riistvara, kasutaja 
JOIN r_paigaldus ON id = r_paigaldus.kasutaja_id;

How the query result should end up looking like:

+---------------+---------+----------+----------------+-----------+--------------+
| kasutaja_nimi | eesnimi | perenimi | r_nimetus      | seeria_nr |paigalduse_aeg|
+---------------+---------+----------+----------------+-----------+--------------+
| jann12        | Jaan    | Jaanik   | Latitude L2100 |  33333333 |2010-10-10    |
| juku23        | Juku    | Juust    | Latitude L2110 |  44444444 |2010-10-01    |
+---------------+---------+----------+----------------+-----------+--------------+

How it looks like:

+---------------+---------+----------+----------------+-----------+----------------+
| kasutaja_nimi | eesnimi | perenimi | r_nimetus      | seeria_nr | paigalduse_aeg |
+---------------+---------+----------+----------------+-----------+----------------+
| juku23        | Juku    | Juust    | Latitude L2100 |  33333333 | 2010-01-01     |
| juku23        | Juku    | Juust    | Latitude L2110 |  44444444 | 2010-01-01     |
| juku23        | Juku    | Juust    | Latitude L2100 |  33333333 | 2010-10-01     |
| juku23        | Juku    | Juust    | Latitude L2110 |  44444444 | 2010-10-01     |
| jann12        | Jaan    | Jaanik   | Latitude L2100 |  33333333 | 2010-01-01     |
| jann12        | Jaan    | Jaanik   | Latitude L2110 |  44444444 | 2010-01-01     |
| jann12        | Jaan    | Jaanik   | Latitude L2100 |  33333333 | 2010-10-10     |
| jann12        | Jaan    | Jaanik   | Latitude L2110 |  44444444 | 2010-10-10     |
+---------------+---------+----------+----------------+-----------+----------------+

Are you talking about mysql _or_ sql-server? In either case, `distinct` probably works as designed and your assumptions are just not correct. — Marvin, May 29 '16 at 11:18
These results are correct, you have distinct value combinations when looking at `r_nimetus`, `seeria_nr` and `paigalduse_aeg`. `Latitude L2100` and `Latitude L2110` combined with `33333333` and `44444444` combined with `2010-01-01` and `2010-10-01`. — gmiley, May 29 '16 at 11:19
Are you looking for this? http://stackoverflow.com/questions/6127338/sql-mysql-select-distinct-unique-but-return-all-columns — Marvin, May 29 '16 at 11:21
Does the final result set contain entries for each date? Please explain your expected output. By the way, **DISTINCT** works for the whole row not for the column it precedes. — 1000111, May 29 '16 at 11:23
Yeah I realise my error now, the assignment I was given was missing a pretty important part heh. The end result also had to limit the query results to the latest input from both users ie. the latest from juku23 and same for jann12. Case closed, thank you! — Oskar V, May 29 '16 at 11:27
Your expected results do not really make much sense unless you have some other criteria in another table we do not know about. For `jann12` you have `Latitude L2100` (the lower value in the grouping), `33333333` (the lower value of the grouping), and `2010-10-10` (the upper value of the grouping). And for `joku23` you have `Latitude 2110` (the upper value in the grouping), `44444444` (the upper value in the grouping), and `2010-10-01` (the upper value in the grouping). — gmiley, May 29 '16 at 11:29

score 2 · Accepted Answer · answered May 29 '16 at 11:41

First of all this query works good because the seeria_nr and paigalduse_aeg is different as you can see so DISTINCT cannot filter out them.

You can use GROUP BY to get what you want:

GROUP BY
    b.kasutaja_nimi
    ,b.eesnimi
    ,b.perenimi
    ,a.r_nimetus

this will brings to you the result that you execept - but remeber that seeria_nr and paigalduse_aeg will be showing randomly values.

score 1 · Answer 2 · answered May 29 '16 at 12:34

Use join correctly. Simple rule: Never use commas in the FROM clause. Always use explicit JOIN syntax.

SELECT kasutaja_nimi, eesnimi, perenimi, r_nimetus, seeria_nr, paigalduse_aeg 
FROM kasutaja k JOIN
     r_paigaldu rp 
     ON k.id = rp.kasutaja_id JOIN
     riistvarar r
     ON r.id = rp.riistvara_id;

This will probably eliminate the need for select distinct. In addition, you should use table aliases and qualify all your column names.

DISTINCT does not remove duplicates

2 Answers2