CODEWITHSUNDEEP
sqlsql-server
carl Brooks
carl Brooks

Reputation: 315

ROW_NUMBER() ORDER BY, can't use column within same SELECT statement

I have a procedure below that calls onto a view to create a league table:

CREATE PROCEDURE [dbo].[League_Table_Insert]
    @LeagueName VARCHAR(30)
AS
SET NOCOUNT ON
BEGIN

    DECLARE @LeagueID INT

    SELECT 
    @LeagueID = LeagueID FROM dbo.League
    WHERE LeagueName = @LeagueName

    SELECT [TeamName], [Played], [Wins], [Loss], [Draws], [Points], [Goals_Scored], [Goals_Against], [Goal_Difference]  
    FROM League_Table
    WHERE LeagueID = @LeagueID
    ORDER BY Points DESC, Goal_Difference DESC;

END

VIEW:

CREATE VIEW League_Table
    AS
    SELECT f.LeagueID, t.TeamName, 
    SUM(
        CASE WHEN f.HomeScore IS NOT NULL THEN 1 ELSE 0 END
    ) AS Played,
        SUM(
        CASE        
            WHEN t.TeamID = f.HomeTeamID THEN
                --Home Fixture
                CASE
                 WHEN f.HomeScore > f.AwayScore THEN 1 
                 ELSE 0
                 END
            WHEN t.TeamID = f.AwayTeamID THEN
                CASE
                 WHEN f.AwayScore > f.HomeScore THEN 1
                 ELSE 0 
                 END
        END
        ) AS Wins, 
            SUM(
        CASE        
            WHEN t.TeamID = f.HomeTeamID THEN
                --Home Fixture
                CASE
                 WHEN f.HomeScore < f.AwayScore THEN 1 
                 ELSE 0
                 END
            WHEN t.TeamID = f.AwayTeamID THEN
                CASE
                 WHEN f.AwayScore < f.HomeScore THEN 1 
                 ELSE 0
                 END
        END
        ) AS Loss, 
        SUM(CASE WHEN f.HomeScore = f.AwayScore THEN 1 ELSE 0 END) as Draws, 
        SUM(
        CASE 
            WHEN t.TeamID = f.AwayTeamID THEN
                --Away Fixture
                CASE
                    WHEN f.AwayScore > f.HomeScore THEN 3
                    WHEN f.AwayScore = f.HomeScore THEN 1
                    ELSE 0
                END
            WHEN t.TeamID = f.HomeTeamID THEN
                --Home Fixture
                CASE
                    WHEN f.HomeScore > f.AwayScore THEN 3 
                    WHEN f.HomeScore = f.AwayScore THEN 1
                    ELSE 0
                END
        END
    ) AS Points, 
        SUM(
        CASE
            WHEN t.TeamID = f.HomeTeamID THEN f.HomeScore
            -- Home Fixture
            WHEN t.TeamID = f.AwayTeamID THEN f.AwayScore
            -- Away Fixture
            END
            ) AS Goals_Scored,

        SUM(
        CASE
            WHEN t.TeamID = f.HomeTeamID THEN f.AwayScore
            -- Home Fixture
            WHEN t.TeamID = f.AwayTeamID THEN HomeScore
            -- Away Fixture
            END
            ) AS Goals_Against,
         SUM(
          CASE
          WHEN t.TeamID = f.HomeTeamID THEN       
              CASE
              WHEN f.HomeScore IS NOT NULL THEN f.HomeScore - f.AwayScore
          -- Home Fixture
              END
          WHEN t.TeamID = f.AwayTeamID THEN 
              CASE
              WHEN f.AwayScore IS NOT NULL THEN f.AwayScore - f.HomeScore
          -- Away Fixture
              END
          END
        ) AS Goal_Difference
    FROM dbo.Team t
    --Season TBC
    INNER JOIN dbo.Fixture f ON t.TeamID IN (f.HomeTeamID, f.AwayTeamID)
    GROUP BY f.LeagueID, t.TeamName

Below is what it outputs:

enter image description here

I want to include another field in the view which will contain each team's league position. My issue is with the ORDER BY when using ROW_NUMBER as I'm not sure what to set this to as I can't set it to 'Points DESC' and 'Goal_Difference DESC' as I'm using it in the same SELECT statement to create it. My question is what do I set the ORDER BY to in the code below to output correct league positions?

ROW_NUMBER() OVER (PARTITION BY LeagueID ORDER BY ...)  AS Position,

Upvotes: 1

Views: 75

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

The easiest approach may be a subquery (derived table):

CREATE VIEW League_Table AS
select 
  data.*, 
  row_number() over (partition by leagueid 
                     order by points desc, goal_difference desc) as position,
from
(
  SELECT f.LeagueID, t.TeamName, 
  SUM(
      CASE WHEN f.HomeScore IS NOT NULL THEN 1 ELSE 0 END
     ) AS Played,
  SUM(
  ...
  GROUP BY f.LeagueID, t.TeamName
) data

Upvotes: 3

xdd
xdd

Reputation: 545

Make one more select

select *, ROW_NUMBER() OVER (PARTITION BY LeagueID ORDER BY Points DESC, Goal_Difference DESC)  AS Position
    from <your select statement>

Upvotes: 3

Related Questions