Select record if value larger than previous in SQL

I have this T-SQL query

WITH CTE AS 
(
    SELECT  
        m.Season AS 'Season',
        SUM(bi.Runs) AS 'Runs',
        p.LastName + ' ' + SUBSTRING(p.FirstName, 1, 1) AS 'PlayerName' 
    FROM Player p
    JOIN BatInnings bi on bi.fk_Player_Id = p.id  
    JOIN Innings i on i.Id = bi.fk_Innings_Id
    JOIN Team t on t.id = i.fk_Team_Id
    JOIN Match m on m.id = i.fk_Match_Id
    WHERE   
        (p.id = @playerId OR @playerId IS NULL) 
        AND m.MatchType IN (@matchType1, @matchType2, @matchType3) 
        AND (i.fk_Team_Id = @teamId OR @teamId IS NULL) 
        AND (t.fk_Club_Id = @clubId OR @clubId IS NULL)
    GROUP BY 
        m.season, p.LastName + ' ' + SUBSTRING(p.FirstName, 1, 1)
) 
SELECT CTE.* 
FROM CTE 
WHERE CTE.Runs = (SELECT MAX(CTE2.Runs) 
                  FROM CTE CTE2 
                  WHERE CTE2.Season = CTE.Season) 
ORDER BY CTE.Season

That pulls out a list based on the highest runs scorer for each season. The result will look like this.

Season   Runs   Player
1990/91  689    Todd D
1991/92  617    Grantham N
1992/93  838    Todd D
1993/94  532    Todd D
1994/95  628    Todd D
1995/96  584    Downer M
1996/97  743    Todd D
1997/98  742    Brown S
1998/99  841    Todd D
1999/00  902    Hart M

I want to further this query to then pull out where each record is higher than the previous selected. So using the list previous, the results from this query would then resemble

Season   Runs   Player
1990/91  689    Todd D
1992/93  838    Todd D
1998/99  841    Todd D
1999/00  902    Hart M

Happy to provide more information if required.

Thanks

3 answers

  • answered 2018-05-16 04:24 Pham X. Bach

    You could use window function MAX() OVER () to get maximum runs until that season for comparing:

    WITH CTE AS (
        SELECT  m.Season AS 'Season',
            SUM(bi.Runs) AS 'Runs',
            p.LastName + ' ' + SUBSTRING(p.FirstName, 1, 1) AS 'PlayerName' 
        FROM Player p
        JOIN BatInnings bi on bi.fk_Player_Id = p.id  
        JOIN Innings i on i.Id = bi.fk_Innings_Id
        JOIN Team t on t.id = i.fk_Team_Id
        JOIN Match m on m.id = i.fk_Match_Id
        WHERE (p.id = @playerId OR @playerId IS NULL) 
            AND m.MatchType IN (@matchType1, @matchType2, @matchType3) 
            AND (i.fk_Team_Id = @teamId OR @teamId IS NULL) 
            AND (t.fk_Club_Id = @clubId OR @clubId IS NULL)
        GROUP BY m.season, p.LastName + ' ' + SUBSTRING(p.FirstName, 1, 1)
    ) 
    , cte1 AS 
    (
        SELECT *
        FROM CTE c
        WHERE Runs = (
            SELECT MAX(Runs) 
            FROM CTE 
            WHERE Season = c.Season
        ) 
    )
    , cte2 AS 
    (
        SELECT season, COALESCE(MAX(runs) OVER (ORDER BY season), 0) AS max_runs_to_season
        FROM cte1 
    )
    SELECT *
    FROM cte1 c
    WHERE runs >= (
        SELECT max_runs_to_season
        FROM cte2 
        WHERE season = c.season
    )
    ORDER BY season;
    

  • answered 2018-05-16 05:42 Sahi

    Try this:

    ;with cte
    AS
    (
     SELECT '1990/91' AS Season, 689 AS Runs, 'Todd D' AS Player
      Union All
     SELECT '1991/92' AS Season, 617 AS Runs, 'Grantham N' AS Player
      Union All
     SELECT '1992/93' AS Season, 838 AS Runs, 'Todd D' AS Player
      Union All
     SELECT '1993/94' AS Season, 532 AS Runs, 'Todd D' AS Player
      Union All
     SELECT '1994/95' AS Season, 628 AS Runs, 'Todd D' AS Player
      Union All
     SELECT '1995/96' AS Season, 584 AS Runs, 'Downer M' AS Player
      Union All
     SELECT '1996/97' AS Season, 743 AS Runs, 'Todd D' AS Player
     Union All
     SELECT '1997/98' AS Season, 742 AS Runs, 'Brown S' AS Player
      Union All
     SELECT '1998/99' AS Season, 841 AS Runs, 'Todd D' AS Player
     Union All
    SELECT '1999/00' AS Season, 902 AS Runs, 'Hart M' AS Player
     )
    
    ,cte2
    AS
    (
      SELECT *,
      MAX(Runs) OVER(Order By (Select NULL) ROWS BETWEEN UNBOUNDED PRECEDING AND 
      CURRENT ROW) As PreMax
      FROM cte
    )
    
     Select Season,Runs,Player from Cte2
     Where Runs>=PreMax
    

    SQL Fiddle: http://sqlfiddle.com/#!18/c6e8e/9

  • answered 2018-05-16 08:29 Bart Hofland

    I'm not that familiar with window functions in SQL, so my answer only uses CTEs and subqueries.

    I put your main query in a new CTE called [SeasonResults] and extended the query from there.

    A new CTE called [MaxSeasonResults] determines the best result per season.

    WITH
        [CTE] AS
        (
            SELECT  
                m.Season AS 'Season',
                SUM(bi.Runs) AS 'Runs',
                p.LastName + ' ' + SUBSTRING(p.FirstName, 1, 1) AS 'PlayerName' 
            FROM Player p
            JOIN BatInnings bi on bi.fk_Player_Id = p.id  
            JOIN Innings i on i.Id = bi.fk_Innings_Id
            JOIN Team t on t.id = i.fk_Team_Id
            JOIN Match m on m.id = i.fk_Match_Id
            WHERE   
                (p.id = @playerId OR @playerId IS NULL) 
                AND m.MatchType IN (@matchType1, @matchType2, @matchType3) 
                AND (i.fk_Team_Id = @teamId OR @teamId IS NULL) 
                AND (t.fk_Club_Id = @clubId OR @clubId IS NULL)
            GROUP BY 
                m.season, p.LastName + ' ' + SUBSTRING(p.FirstName, 1, 1)
        ),
        [SeasonResults] AS
        (
            SELECT CTE.* 
            FROM CTE 
            WHERE CTE.Runs = (SELECT MAX(CTE2.Runs) 
                              FROM CTE CTE2 
                              WHERE CTE2.Season = CTE.Season) 
            ORDER BY CTE.Season
        ),
        [MaxSeasonResults] AS
        (
            SELECT
                [Season],
                [MaxRuns] = MAX([Runs])
            FROM [SeasonResults]
            GROUP BY [Season]
        )
    SELECT R.*
    FROM
        [SeasonResults] AS R
        JOIN [MaxSeasonResults] AS M ON
            M.[Season] = R.[Season]
            AND M.[MaxRuns] = R.[Runs]
    WHERE
        M.[MaxRuns] > (SELECT COALESCE(MAX(_M.[MaxRuns]), 0)
                       FROM [MaxSeasonResults] AS _M
                       WHERE _M.[Season] < M.[Season])
    ORDER BY R.[Season];
    

    Perhaps more optimizations/refactorings are possible, but I had to test my query using a temporary table containing your given result set, so it was quite hard for me to check and test for any further optimizations. Sorry about that.