Removing duplicates from one column only

Question

So I would have something like this. Its the outcome a query into two tables.

+------------------+------------------------------------+
| character_name   | title                              |
+------------------+------------------------------------+
| derp             | a                                  |
| derp             | b                                  |
| derp             | c                                  | 
| herp             | a                                  |
| herp             | b                                  |
| herp             | c                                  |
+-------------------------------------------------------+

and I want this

+------------------+------------------------------------+
| character_name   | title                              |
+------------------+------------------------------------+
| derp             | a                                  |
|                  | b                                  |
|                  | c                                  | 
| herp             | a                                  |
|                  | b                                  |
|                  | c                                  |
+-------------------------------------------------------+

Is this possible?

Answer 1

Is this what you mean?

select case when i.rnk = 1 THEN i.character_name  ELSE '' END as NAME
, i.title
from(
    select *
    , row_number() over (partition by character_name order by title) as rnk
    from t1
) i
order by i.character_name 

First name will get the value 1, which you show in the result, anything else you hide.

Update - MySql version:

SET @r_name:='';

select name, title FROM (
    select @r_name:=CASE WHEN @r_name = name THEN '' 
    ELSE name
    END AS name
    , @r_name:=name
    , title
    FROM
    t1
) t2