Suppressing a repeated column value in PostgreSQL?

Question

For example if we have the values:

username             Category
-------------------- ----------
Brian                cs
Tom                  cs
Joe                  cs
Allen                cs
Bill                 ts
Steven               ts
Fred                 ts
Ted                  ts

I Need an Output as

username             Category
-------------------- ----------
Brian                cs
Tom                  
Joe                  
Allen                
Bill                 ts
Steven               
Fred                 
Ted      

I have Tried

1) T-SQL How to select rows without duplicate values from one column?

2) http://www.rajapet.com/2010/04/suppressing-repeated-column-value-in.html

3) How to select records without duplicate on just one field in SQL?

Nothing Work well ( What i Expected)

Is there any other way ?

Answer 1

You can use a window function for this:

select username, 
       case when rn = 1 then cat else '' end as category
from (
   select username, 
          category as cat, 
          row_number() over (partition by category order by username) as rn
   from the_table
) t
order by cat, username;

SQLFiddle: http://sqlfiddle.com/#!15/423ed/4

Answer 2

Here's an alternative way, using a subquery. Note that both answers will give the category value to the first username alphabetically i.e. Allen will get cs, and Bill ts.

select a.username,b.category from
foo a
left join
(select category,min(username) as username from foo group by category) b
on b.username = a.username
order by a.category,a.username

[EDIT] If you want to assign to the first instance in your table (i.e. return what you put in your question), you can do something like this:

    with foobar as 
        (select username,category, row_number() over() as rownum from foo )

   select a.username,coalesce(b.category,'') as category from
    foobar a
    left join
    (select category,min(rownum) as rownum from foobar group by category) b
    on b.rownum = a.rownum
    order by a.rownum,a.username