Dazzle Vision Dazzle Vision - 5 months ago 9
SQL Question

How to select certain rows

I want to create a promotion and relegation between two leagues where the top two teams from LeagueID 2 moves up to LeagueID 1 and the bottom two teams from LeagueID 1 moves down to LeagueID 2.

My question is how to select those teams, bearing in mind the number of team per league may change in the future, so I cannot for relegating teams use a where clause on position. I can use a where clause for promotion no problem for position 1 and 2 from LeagueID 2 but if there is a way to do it from a select and a count then I will go for that method as well.

I know all I need to do when we select the correct teams is just update their LeagueIDs.

enter image description here

LATEST UPDATE:

UPDATE dbo.League
SET LeagueId = 2
WHERE LeagueId = 1
AND Position IN
(
SELECT TOP 2 Position
FROM dbo.League_Table
WHERE LeagueID = 1
ORDER BY Position DESC
)

UPDATE dbo.League
SET LeagueId = 1
WHERE LeagueId = 2
AND Position IN
(
SELECT TOP 2 Position
FROM dbo.League_Table
WHERE LeagueID = 2
ORDER BY Position
)


'Team' Table: This table is a reference for all teams and which league they play in:

CREATE TABLE [dbo].[Team]
(
[TeamID] TINYINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
[TeamAbbreviation] CHAR(3) UNIQUE,
[TeamName] VARCHAR(50) UNIQUE,
[LeagueID] TINYINT CONSTRAINT FK_Team_League FOREIGN KEY REFERENCES League(LeagueID),
[CountryID] TINYINT CONSTRAINT FK_Team_Country FOREIGN KEY REFERENCES Country(CountryID)
)


'League' Table: Reference to all the leagues:

CREATE TABLE [dbo].[League]
(
[LeagueID] TINYINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
[LeagueName] VARCHAR(30) UNIQUE,
[MinLeagueWeight] INT,
[MaxLeagueWeight] INT,
[CountryID] TINYINT CONSTRAINT FK_League_Country FOREIGN KEY REFERENCES Country(CountryID),
[ParentLeagueID] TINYINT CONSTRAINT FK_League_ParentLeague FOREIGN KEY REFERENCES League(LeagueID)
)


'League_Table' table: This is the league table made up from a view (as seen in the screenshot above):

SELECT [Position], [TeamName], [Played], [Wins], [Loss], [Draws], [Points], [Goals_Scored], [Goals_Against], [Goal_Difference], [LeagueID]
FROM League_Table

Answer

You can order descending for the bottom two, no matter how many there are

SELECT TOP 2 * 
FROM Teams 
WHERE LeagueID = 1 
ORDER BY Position DESC

(Sidenote: Im hoping your Position field is calculated, because if you maintain it that could affect the integrity of your data)


Your 2 queries are fine, and combining them into one, although possible, will be less clear. You're simply missing a WHERE clause in the subqueries

UPDATE dbo.League
SET LeagueId = 2
WHERE LeagueId  = 1
AND Position IN
(
    SELECT TOP 2 Position
    FROM dbo.League 
    WHERE LeagueID = 1
    ORDER BY Position DESC
)

UPDATE dbo.League
SET LeagueId = 1
WHERE LeagueId  = 2
AND Position IN
(
    SELECT TOP 2 Position
    FROM dbo.League 
    WHERE LeagueID = 2
    ORDER BY Position
)

Another try at this - this time with a single query. H/T to @Jens for providing a test case.

If you first work out how every team's league position changes:

SELECT LeagueID, TeamName, case
    when league>1 AND RANK() OVER (PARTITION BY league ORDER BY position ASC) <= 2 THEN -1
    when league<2 AND RANK() OVER (PARTITION BY league ORDER BY position DESC) <= 2 THEN 1
    else 0
end as leaguechange
FROM dbo.League_Table

(Note I have put in an extra 2 clauses there - league>1 to stop a team being promoted to league 0 and league<2 to stop teams being demoted to league 3 - both of these can be adjusted accordingly)

Once you have that, it can be used as a subquery within a single UPDATE:

UPDATE dbo.Team
SET LeagueID = LeagueID + leaguechange
FROM (
    SELECT LeagueID, TeamName, case
        when league>1 AND RANK() OVER (PARTITION BY leagueID ORDER BY position ASC) <= 2 THEN -1
        when league<2 AND RANK() OVER (PARTITION BY leagueID ORDER BY position DESC) <= 2 THEN 1
        else 0
    end as leaguechange
    FROM dbo.League_Table
) c
INNER JOIN dbo.Team t
 ON c.LeagueId = t.LeagueID
 AND c.TeamName = t.TeamName