CODEWITHSUNDEEP
sqlsql-server
user1189352
user1189352

Reputation: 3885

Trying to select a range.. for instance 10-20 of top results

ive googled and searched this site and nothing seems to work, seems like it doesn't like the syntax from everything i found.. seems pretty simple though so it's getting frustrating. can anyone help? would like it to use the topRange and bottomRange variables I've created

ALTER PROCEDURE [dbo].[getTopFantasyPlayersByPositionSeason]
    @top int,
    @position varchar(3),
    @topRange int,
    @bottomRange int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    IF @position = 'QB' OR @position = 'RB' OR @position = 'WR' OR @position = 'TE' OR @position = 'K' OR @position = 'D' OR @position = 'TE'
        BEGIN
            SELECT TOP (@top) *
            FROM nflPassers_season
            WHERE position=@position AND points > 0
            ORDER BY points DESC
        END

END

Upvotes: 1

Views: 92

Answers (2)

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

ALTER PROCEDURE [dbo].[getTopFantasyPlayersByPositionSeason]
    @top INT
  , @position VARCHAR(3)
  , @topRange INT
  , @bottomRange INT
AS 
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
        SET NOCOUNT ON;

    -- Insert statements for procedure here
        IF @position IN ( 'QB', 'RB', 'WR', 'TE', 'K', 'D', 'TE' ) 
            BEGIN
            ;
                WITH    c AS ( SELECT   rn = ROW_NUMBER() OVER ( ORDER BY points DESC)
                                      , firstname
                               FROM     nflPassers_season
                               WHERE    position = @position
                                        AND points > 0
                             )
                    SELECT  firstname
                    FROM    c
                    WHERE   rn BETWEEN @toprange AND @bottomRange

            END

    END

Upvotes: 1

Hart CO
Hart CO

Reputation: 34784

Basic pagination pre-SQL 2012:

SELECT *
FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY points DESC) AS RowRank
      FROM YourTable
      )AS sub
WHERE Rowrank BETWEEN @Variable1 AND @Variable2

If you want a range of results per some grouping, ie by player, you can add PARTITION BY to the ROW_NUMBER():

SELECT *
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY SomeField ORDER BY points DESC) AS RowRank
      FROM YourTable
      WHERE position = @position
        AND points > 0
      ) AS sub
WHERE Rowrank BETWEEN @Variable1 AND @Variable2

That would make the ranking begin at 1 for each item in the PARTITION BY clause.

Upvotes: 1

Related Questions