Question: I am learning SQL, and I am trying to get a job. I have the following table in MS SQL Server 2014:

The table is called Game with the following fields: Name, Stage #, Score.

My goal is to write the winner of each stage with his/her name (winner of the stage is the person who earned the maximum score).

Here is the original table:

```
Name    Stage #         Score
George     A              10
Joe        A              10
Pete       A               9
Jane       B               7
Sally      B               6

```

Here is what the output should look like:

```
Winner Name    Score         Stage
George           10             A
Joe              10             A
Jane              7             B

```

How can I accomplish this task? A colleague referred me to <http://sqlfiddle.com/> to help me figure this out, but this website is apparently not working well for MS SQL Server 2014 or MS SQL Server 2008. Therefore, can I receive some assistance here?

I understand some of the basic functions, such as SELECT, WHERE, GROUP BY, JOIN, and HAVING, but I am having trouble putting it all together to get the correct three-line output that I desire above.

Answer: 
There are tens of different ways to do this in SQL. Lets start with the simple correlated subquery (mind the fancy name, once you see and write a few of them, they are very easy to understand):

```
select                                -- show
    g.name, g.stage, g.score          -- all data
from                                  -- from
    game as g                         -- the table
where                                 -- where
    not exists                        -- there isn't
        ( select *                    -- any other 
          from game as g2             -- from the same table
          where g2.stage = g.stage    -- and the same stage
            and g2.score > g.score    -- with bigger score
        ) ;

```

Another simple way would be to first find the biggest score for each stage using `GROUP BY` (in a subquery, either a derived table or a CTE) and then `JOIN` back to the original table:

```
-- using derived table
select      
    g.name, g.stage, g.score 
from      
    game as g 
  join
    ( select stage, max(score) as score
      from game
      group by stage
    ) as m
  on  m.stage = g.stage
  and m.score = g.score ;

-- using CTE
with stage_max as
    ( select stage, max(score) as score
      from game
      group by stage
    ) 
select      
    g.name, g.stage, g.score 
from      
    game as g 
  join
    stage_max as m
  on  m.stage = g.stage
  and m.score = g.score ;

```

A more modern way would be to use window functions (available in your SQL Server versions), i.e. the `RANK()` function, so first you get the "rank" of everyone per stage and then select only the ones with `rank=1`. This can also be done with either a derived table or a CTE:

```
-- window functions, using derived table
select      
    w.name, w.stage, w.score 
from      
    ( select name, stage, score,
             rnk = rank() over (partition by stage
                                order by score desc)
      from game
    ) as w
where
    w.rnk = 1 ;

-- window functions, using CTE
with ranking as
    ( select name, stage, score,
             rnk = rank() over (partition by stage
                                order by score desc)
      from game
    ) 
select      
    w.name, w.stage, w.score 
from      
    ranking as w 
where
    w.rnk = 1 ;

```
Welcome to the site. Instead of ascii tables with data it is better to provide create table statements and insert statements like:

```
create table game 
(    name varchar(20) not null
,    stage# char(1) not null
,    score int not null
,        primary key (name, stage#) -- assumed this one
);

insert into game (name, stage#,score) 
values ('George','A',10)
     , ('Joe','A',10)
     , ('Pete','A',9)
     , ('Jane','B',7)
     , ('Sally','B',6);

```

This way it becomes much easier for others to reproduce your scenario.

As @YperSillyCubeᵀᴹ points out there are a lot of ways you can solve this problem. Here's one example that illustrates using a join against a derived table:

```
select a.name as winner_name, a.score, a.stage# 
from game as a 
join ( 
    select stage#, max(score) as max_score 
    from game 
    group by stage# 
) as b 
    on a.stage# = b.stage# 
   and a.score = b.max_score;

```