SQL Server - How to use multiple column values in where clause

Question

I have a sports stats DB. I want to get the top five performers from each season for each team. So my club has five teams.

I have a column which contains seasons (1990, 1991, 1992 etc). I have a column which contains my teams (team1, team2, team3 etc).

So what I really want is:

1990 team1 Jones
1990 team1 Smith
1990 team1 Cross
1990 team1 Billy
1990 team1 Alex

then

1990 team2 Craig
1990 team2 Alan
...

What it feels like I need is a foreach in the where clause, but I know that's wrong.

If I have no where clause, then it just gets the overall top five ever.

I can do:

where team = team1 AND season = 1990
where team = team1 AND season = 1991
...
where team = team3 AND season = 1990

But I'd have to do that by hand for every season and every team. I know there must be a better way :)

Answer 1

you can look into the OVER clause

WITH CTE AS (
SELECT name, ROW_NUMBER() OVER(PARTITION BY season, team ORDER BY performance DESC) AS RN
FROM table
)
SELECT name
FROM CTE
where  RN<=5

something like that

Answer 2

Prepare sample data:

declare @Year table
(
    Year int
)

insert @Year values (1990), (1991), (1992)

declare @Team table
(
    Team int,
    Player varchar(10)
)

insert @Team values 
    (1, 'a'), 
    (1, 'b'),
    (1, 'c'), 
    (1, 'd'),
    (1, 'e'), 
    (1, 'f'),
    (1, 'g'), 
    (2, 'p'), 
    (2, 'q'),
    (2, 'r'), 
    (2, 's'),
    (2, 't'), 
    (2, 'u'),
    (2, 'v')

declare @Player table
(
    Id int identity,
    Year int,
    Team int,
    Name varchar(10),
    Score int
)
insert
    @Player
select 
    y.Year,
    t.Team,
    t.Player,
    60 + cast(40 * rand(checksum(newid())) as int) as Score
from 
    @Year as y
cross join
    @Team as t
order by 1, 2, 3

Sample data:

| Id | Year | Team | Name | Score |
|----|------|------|------|-------|
| 1  | 1990 | 1    | a    | 99    |
| 2  | 1990 | 1    | b    | 78    |
| 3  | 1990 | 1    | c    | 80    |
| 4  | 1990 | 1    | d    | 94    |
| 5  | 1990 | 1    | e    | 77    |
| 6  | 1990 | 1    | f    | 96    |
| 7  | 1990 | 1    | g    | 85    |
| 8  | 1990 | 2    | p    | 97    |
| 9  | 1990 | 2    | q    | 67    |
| 10 | 1990 | 2    | r    | 68    |
| 11 | 1990 | 2    | s    | 61    |
| 12 | 1990 | 2    | t    | 78    |
| 13 | 1990 | 2    | u    | 88    |
| 14 | 1990 | 2    | v    | 66    |
| 15 | 1991 | 1    | a    | 93    |
| 16 | 1991 | 1    | b    | 82    |
| 17 | 1991 | 1    | c    | 96    |
| 18 | 1991 | 1    | d    | 90    |
| 19 | 1991 | 1    | e    | 67    |
| 20 | 1991 | 1    | f    | 60    |
| 21 | 1991 | 1    | g    | 64    |
| 22 | 1991 | 2    | p    | 83    |
| 23 | 1991 | 2    | q    | 77    |
| 24 | 1991 | 2    | r    | 75    |
| 25 | 1991 | 2    | s    | 74    |
| 26 | 1991 | 2    | t    | 91    |
| 27 | 1991 | 2    | u    | 88    |
| 28 | 1991 | 2    | v    | 69    |
| 29 | 1992 | 1    | a    | 96    |
| 30 | 1992 | 1    | b    | 98    |
| 31 | 1992 | 1    | c    | 70    |
| 32 | 1992 | 1    | d    | 68    |
| 33 | 1992 | 1    | e    | 84    |
| 34 | 1992 | 1    | f    | 70    |
| 35 | 1992 | 1    | g    | 87    |
| 36 | 1992 | 2    | p    | 60    |
| 37 | 1992 | 2    | q    | 77    |
| 38 | 1992 | 2    | r    | 76    |
| 39 | 1992 | 2    | s    | 91    |
| 40 | 1992 | 2    | t    | 80    |
| 41 | 1992 | 2    | u    | 70    |
| 42 | 1992 | 2    | v    | 62    |

Result:

| Year | Team | Name | Score |
|------|------|------|-------|
| 1990 | 1    | d    | 98    |
| 1990 | 1    | b    | 94    |
| 1990 | 1    | c    | 93    |
| 1990 | 1    | g    | 79    |
| 1990 | 1    | f    | 78    |
| 1990 | 2    | q    | 99    |
| 1990 | 2    | r    | 85    |
| 1990 | 2    | t    | 82    |
| 1990 | 2    | v    | 80    |
| 1990 | 2    | s    | 69    |
| 1991 | 1    | c    | 95    |
| 1991 | 1    | f    | 93    |
| 1991 | 1    | a    | 88    |
| 1991 | 1    | g    | 81    |
| 1991 | 1    | b    | 73    |
| 1991 | 2    | r    | 99    |
| 1991 | 2    | u    | 93    |
| 1991 | 2    | t    | 89    |
| 1991 | 2    | p    | 86    |
| 1991 | 2    | v    | 80    |
| 1992 | 1    | e    | 90    |
| 1992 | 1    | g    | 88    |
| 1992 | 1    | f    | 84    |
| 1992 | 1    | a    | 80    |
| 1992 | 1    | b    | 77    |
| 1992 | 2    | p    | 94    |
| 1992 | 2    | t    | 89    |
| 1992 | 2    | v    | 86    |
| 1992 | 2    | u    | 77    |
| 1992 | 2    | q    | 74    |

Query:

;
with
sort as
(
    select
        row_number() over (partition by p.Year, p.Team order by p.Score desc) as RowId,
        p.Year,
        p.Team,
        p.Name,
        p.Score
    from 
        @Player as p
)
select
    s.Year,
    s.Team,
    s.Name,
    s.Score
from
    sort as s
where
    s.RowId <= 5

Answer 3

Hope this would suffice your requirement. Just replace the "Table_A" to actual table name that you have and modify column names accordingly.

;WITH TeamStat AS(
SELECT Season
    ,Team
    ,Names, PlayerScore
    ,ROW_NUMBER() OVER (PARTITION BY (Season) ORDER BY PlayerScore) AS Orders
FROM TABLE_A
)
SELECT * from TeamStat where Orders <=5

Answer 4

You can use ranking functions like ROW_NUMBER() with PARTITION BY clause.

;WITH CTE AS (
             SELECT team,player,season
                    ROW_NUMBER() OVER(PARTITION BY team,season ORDER by Score DESC) as rnk
             FROM sports_stats_table
            )

SELECT team,player,season
FROM CTE
WHERE rnk <=5
ORDER BY rnk desc,season,team

For evaluating performance to select top performers I am assuming a column called Score in the table

Answer 5

Use ROW_NUMBER() AND Partition By : Partition By will group your data in given parameter and give unique id to it.

Declare @tblTest AS Table
(
    Id INT,
    Season INT,
    Team VARCHAR(50),
    Name VARCHAR(50)
)

INSERT INTO @tblTest VALUES(1,2001,'Team1','Name1')
INSERT INTO @tblTest VALUES(2,2001,'Team1','Name2')
INSERT INTO @tblTest VALUES(3,2001,'Team1','Name3')
INSERT INTO @tblTest VALUES(4,2001,'Team1','Name4')
INSERT INTO @tblTest VALUES(5,2001,'Team1','Name5')
INSERT INTO @tblTest VALUES(6,2001,'Team1','Name6')
INSERT INTO @tblTest VALUES(7,2001,'Team1','Name7')

INSERT INTO @tblTest VALUES(8,2002,'Team2','Name1')
INSERT INTO @tblTest VALUES(9,2002,'Team2','Name2')
INSERT INTO @tblTest VALUES(10,2002,'Team2','Name3')
INSERT INTO @tblTest VALUES(11,2002,'Team2','Name4')
INSERT INTO @tblTest VALUES(12,2002,'Team2','Name5')
INSERT INTO @tblTest VALUES(13,2002,'Team2','Name6')
INSERT INTO @tblTest VALUES(14,2002,'Team2','Name7')

SELECT 
    Id ,
    Season ,
    Team ,
    Name 
FROM
(
    SELECT 
        Id ,
        Season ,
        Team ,
        Name ,
        ROW_NUMBER() OVER(Partition BY Season,Team Order by  Season,Team,Name) AS PartNo
    FROM
    @tblTest
)X
WHERE X.PartNo<6