How to use rank to get rid of duplicate id?

Question

I have the following two tables:

id  | age  | sex  | key  | sport
293   22     m      a     soccer
987   25     f      b     track
501   27     m      c     swimming 
293   22     m      a     soccer
098   31     f      d     soccer
501   27     m      c     swimming
501   27     m      c     swimming
...

key | name 
a     ed
b     meg
c     ben 
a     ed
d     jenny
c     ben
c     ben
....

I dont want any duplicate rows of ID. I want something like this:

id  | Name | age  | sex  | key  | sport
293   ed      22     m      a     soccer
987   meg     25     f      b     track
501   ben     27     m      c     swimming 
098   jen     31     f      d     soccer

I wrote a query but it doesn't remove the duplicate rows of ID. I tried doing distinct and group by method too and it still wouldn't remove duplicate IDs.

I tried:

SELECT DISTINCT tb1.id, tb1.age, tb2.name, tb1.sport
FROM table1 
INNER JOIN tb2 ON tb1.key = tb2.key

What am I doing wrong? I tried distinct and group by methods but still gives me duplicates.

Answer 1

SELECT DISTINCT tb1.id, tb1.age, tb2.name, tb1.sport FROM table1

This clause will select distinct combinations of these 4 columns and not only distinct ids.

From the above tables: it will consider the below two rows as distinct:-

   a   22   ed   soccer
   a   22   ed  (if any other sport ed plays)

I found a similar post which might be helpful for you: SELECT DISTINCT on one column, with multiple columns in mysql

Answer 2

The reason of duplicate ids that the rows in your second table is also duplicate. So you distinct only on second table, like this

SELECT tb1.id, tb1.age, tb2.name, tb1.sport
FROM table1 
INNER JOIN (SELECT DISTINCT key, name FROM tb2) AS t2 ON tb1.key = t2.key