1

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.

  • "A colleague referred me to sqlfiddle.com to help me figure this out…" – you mean you don't have access to an instance of SQL Server to be able to work out a solution (whether at home or at work/school)? Does that mean that you won't be able to test whatever solution you will be offered here? – Andriy M Jan 14 '16 at 13:31
11

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 ;
| improve this answer | |
4

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

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.

| improve this answer | |
3

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;
| improve this answer | |
0

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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.