CODEWITHSUNDEEP
sqlsql-server
Roger Steinberg
Roger Steinberg

Reputation: 1604

Top 1 Value from multiple columns by group

Situation:

Each id is part of a group and of these id have their favorite game played.

Output structure:

+----+--------+-------+-------+-------+
| id | group  | game1 | game2 | game3 |  
+----+--------+-------+-------+-------+
|  1 | brazil | wow   | clash | dofus |  
|  1 | brazil | fifa  | clash| dofus |  
|  1 | brazil | wow   | wakfu | dofus |  
|  2 | korea  | clash | dofus | clash | 
|  2 | korea  | clash | dofus | clash |  
|  3 | france | wow   | fifa  | nfl   | 
|  3 | france | wow   | fifa  | nfl   |  
+----+--------+-------+-------+-------+

Objective:

I need to take the top 1 value for game1, game2, game3 by group. The top 1 would be the game that appears the most times in the column.

The result should like this:

+--------+--------+-------+-------+
| group  | game1  | game2 | game3 |
+--------+--------+-------+-------+
| brazil | wow    | clash | dofus |
| korea  | clash  | dofus | clash |
| france | wow    | fifa  | nfl   |
+--------+--------+-------+-------+

Data:

create table #t1 (id int,[group] varchar(10),game1 varchar(10),game2 varchar(10),game3 varchar(10))

insert into #t1 values 
(1, 'brazil','wow','clash','dofus'),
(1, 'brazil','fifa','clash','dofus'),
(1, 'brazil','wow','wakfu','dofus'),
(2, 'korea','clash','dofus','clash'),
(2, 'korea','clash','dofus','clash'),
(3, 'france','wow','fifa','nfl'),
(3, 'france','wow','fifa','nfl')

Upvotes: 1

Views: 113

Answers (3)

Tiny Haitian
Tiny Haitian

Reputation: 479

First, if my opinion means anything, rename the column titled group. Though I assume you might have typed it like this for explanation, it might give you an error as it is reserved (or not, if brackets are used). If anything, it'd make it easier to read.

In other news, if you could use CTE's, I'd suggest the following: 

    ;WITH Set1 AS
    (
        SELECT id, GameGroup, game1, 
        ROW_NUMBER() OVER (PARTITION BY [id] ORDER BY id ASC, count(game1) DESC) rn
        FROM #t1
        GROUP BY id, GameGroup, game1
    ), 
    Set2 AS
    (
        SELECT id, GameGroup, game2, 
        ROW_NUMBER() OVER (PARTITION BY [id] ORDER BY id ASC, count(game2) DESC) rn
        FROM #t1
        GROUP BY id, GameGroup, game2
    ),
    Set3 AS
    (
        SELECT id, GameGroup, game3, 
        ROW_NUMBER() OVER (PARTITION BY [id] ORDER BY id ASC, count(game3) DESC) rn
        FROM #t1
        GROUP BY id, GameGroup, game3
    )

    SELECT a.GameGroup, a.game1, b.game2, c.game3
    FROM Set1 a
    INNER JOIN Set2 b
      ON a.id = b. id AND a.rn = b.rn
    INNER JOIN Set3 c
      ON a.id = c.id AND a.rn = c.rn
    WHERE a.rn = 1

Example listed here:

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270993

I am going to suggest cross apply:

select t.group, g1.game1, g2.game2, g3.game3
from (select distinct group
      from #t1 t
     ) t cross apply
     (select top (1) game1
      from #t1 t
      group by game1
      order by count(*) desc
     ) g1 cross apply
     (select top (1) game2
      from #t1 t
      group by game2
      order by count(*) desc
     ) g2 cross apply
     (select top (1) game3
      from #t1 t
      group by game3
      order by count(*) desc
     ) g3;

Upvotes: 2

forpas
forpas

Reputation: 164194

With a CTE that UNIONs all 3 columns to 1 column and then aggregate on it:

with cte as (
    select
      id, [group], gamecol, game, 
      row_number() over (partition by [group], gamecol order by count(*) desc) rn
    from (
      select id, [group], 'game1' gamecol, game1 game from #t1  
      union all
      select id, [group], 'game2', game2 from #t1
      union all
      select id, [group], 'game3', game3 from #t1
    ) t
    group by id, [group], gamecol, game
)
select
  id, [group], 
  max(case when gamecol = 'game1' then game end) game1,
  max(case when gamecol = 'game2' then game end) game2,
  max(case when gamecol = 'game3' then game end) game3
from cte
where rn = 1
group by id, [group]
order by id

See the demo.
Results:

> id | group  | game1 | game2 | game3
> -: | :----- | :---- | :---- | :----
>  1 | brazil | wow   | clash | dofus
>  2 | korea  | clash | dofus | clash
>  3 | france | wow   | fifa  | nfl

Upvotes: 1

Related Questions