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: 
Another solution, that some people find most intuitive (and others don't):

```
SELECT
    [Winner Name] = G.name,              -- The result columns
    G.score,
    Stage = G.stage#
FROM dbo.game AS G
WHERE
    G.score =                            -- For rows where the score equals
    (
        SELECT
            MAX(G2.score)                -- The highest score
        FROM dbo.game AS G2
        WHERE
            G2.stage# = G.stage#         -- Within the same stage
    )
ORDER BY
    G.stage# DESC,
    G.score DESC;

```

Despite the two references to the table, this typically only accesses the table once:

[![Execution plan](https://i.stack.imgur.com/H6MjS.png)](https://i.stack.imgur.com/H6MjS.png)

Remember, SQL is a declarative language: You use it to specify the *results* you want, not *how to get them*.

A suitable index would remove the need for the Sort above.
Here is a solution, hope this will help you.

Given Sample:

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

```

Required Condition:

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

```

Solution:

```
DECLARE @Temp TABLE 
(
    Name  VARCHAR(250),
    Stage VARCHAR(10),
    Score INT 
);

INSERT INTO @Temp
    ( Name, Stage, Score )
VALUES  
    ('George',     'A',              10),
    ('Joe',       'A',              10),
    ('Pete',       'A',               9),
    ('Jane',       'B',               7),
    ('Sally',      'B',               6)

SELECT * FROM @Temp

SELECT 
    b.name,Stage, Score 
FROM
    (
        SELECT Stage, MAX(Score) AS Score 
        FROM @Temp 
        GROUP BY Stage
    ) a
CROSS APPLY 
    (
        SELECT DISTINCT name 
        FROM @Temp [_a] 
        WHERE [_a].Score = a.Score 
        AND [_a].Stage = a.Stage
    ) b
ORDER BY 
    a.Stage,b.Name

```