SQL selecting distinct values from set of values in the same table

user_11951948 0
Viewed 64

I have the following table as a result of my query :

value1     name  
------     ------
 abc       JOHN
 def       JOHN
 mno       JOHN
 mno       JOHN
 abc       JAMES
 abc       JAMES
 def       JAMES
 mno       RICK

In the above table the value mno is repeated twice for JOHN and value abc is repeated twice for JAMES.

The query I used is here :

SELECT tc.value, tr.name
  FROM table1 tc, table2 tr
 WHERE tc.id = tr.roll
 ORDER BY tr.name;

The result i would like to expect is that the duplicate values has to be removed from each name. Result should be something like this:

value1   name
------  ------
 abc     JOHN
 def     JOHN
 mno     JOHN
 abc     JAMES
 def     JAMES
 mno     RICK

I Just want to remove the duplicate value from each name. How can I achieve this ?

distinct group-by mysql sql
2 Answers

Try with distinct as below and also please use standard joining as shown.

SELECT DISTINCT tc.value, tr.name 
FROM table1 tc 
INNER JOIN table2 tr 
ON tc.id = tr.roll 
ORDER BY tr.name;
user_36523450

You can use DISTINCT keyword as you already tagged :

SELECT DISTINCT t1.value, t2.name
  FROM table1 t1
  JOIN table2 t2
    ON t1.id = t2.roll
 ORDER BY t2.name;

or GROUP BY t1.value, t2.name clause, alternatively :

SELECT t1.value, t2.name
  FROM table1 t1
  JOIN table2 t2
    ON t1.id = t2.roll
 GROUP BY t1.value, t2.name   
 ORDER BY t2.name;
user_58413060
Related