Select record if value larger than previous in SQL

sql: selecting rows where column value changed from previous row
how to use values from previous or next rows in a sql server query
sql greater than or equal to and less than
how to compare current value with previous value in sql
how to get previous row value in sql server 2008
sql compare previous row
sql get next row value
between vs greater than performance sql oracle

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

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

SQL Find Minimum Value Greater than Previous Row, ALTER TABLE #tv ADD CONSTRAINT [PK_tv] PRIMARY KEY (businesskey, id); GO SELECT * FROM #tv ORDER BY businesskey, id; GO IF  There are two problems with this query. If the magic number is the last entry, it’ll select the two previous records. And a gap in the sequence will make it select the wrong values too. Try it with 100, 101, and 105, and you’ll see what I mean.

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;

How to Use Values from Previous or Next Rows in a SQL Server Query, How to Use Values from Previous or Next Rows in a SQL Server SELECT * If you're using SQL Server 2012 or higher, it's a little easier. In SQL Server versions prior to 2012, you need to perform a join using a row enumerator to match up rows with previous or next rows. In 2012 and higher, there are two functions, Lag() and Lead(), that greatly simplify the process.

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.

SQL WHERE Clause, The question is easy to answer if the SQL statement mentions the start and SELECT first_name, last_name, date_of_birth FROM employees WHERE It starts at the first entry matching the date range and ends at the last one—all The difference is that the equals operator limits the first index column to a single value. “How to access Previous Row Value and Next Row Value in SELECT statement?” Very interesting question indeed. The matter of the fact, I have written about this in my earlier blog Introduction to LEAD and LAG – Analytic Functions Introduced in SQL Server 2012. Let us quickly understand it in it with the help of script.

Indexing SQL range conditions less than, greater than and between, SQL Comparison operator: A comparison or relational operator is a mathematical symbol which is used to compare between two values. SELECT[column_name| * |expression]<comparison operator> Equal to Operator; Greater than Operator; Less than Operator; Greater Previous: Arithmetic Operator I need to compare the value of previous row with the value of current row in a table in SQL Server 2008. Table name is tbHeight with four (4) colunms. measurements of height is done once a month. the height of current month should be higher than the height from previous month.

SQL Comparison operator, Before we proceed to check each operator try to create “EmployeeDetails” table If we run following SQL statement for the equal operator it will return records where empid equals to 1. SELECT * FROM EmployeeDetails WHERE empid = 1 When we execute the above SQL greater than the operator query, we will get the  You should use LEFT JOIN to avoid excluding rows where there are no times greater than the one of the current row. SELECT i1.id, i1.time AS time, i2.time AS greater_time FROM idtimes AS i1 LEFT JOIN idtimes AS i2 ON i1.id = i2.id AND i2.time > i1.time The problem, as you mentioned, is that you have multiple rows where next_time is greater than time.

SQL Comparison Operators (Equal, Not Equal, Less than, Grater , When evaluating a SELECT statement with a WHERE clause, SQLite uses the set based on the rows in the previous step with columns in the SELECT clause. Less than. > Greater than. <= Less than or equal to. >= Greater than or equal to NOT, reverses the value of other operators such as NOT EXISTS, NOT IN, NOT  To find the next ID, change the max() to a min() and flip the less than to a greater than. You can exploit this feature to efficiently find the upper and lower IDs either side of our target. Plugging the previous query in to a basic select from employees, along with one to find the next row, gives the following:

Comments
  • Does the fact that you are manually selecting all the records that the CTE is returning (and will select other when more 'Seasons" get added defeat the purpose of the CTE in the original post?