Getting a minimum total number of rows either side of a specific row

Question

I have a table of players each having an ID (indexed primary key), a name, and a score. The table is not sorted except by index. e.g.

[dbo].[PlayerScores]
ID | Name | Score
=================
1  | Bob  | 17
2  | Carl | 24
3  | Ann  | 31
4  | Joan | 11
5  | Lou  | 17
6  | Dan  | 25
7  | Erin | 33
8  | Fred | 29

I've defined a leaderboard such that all of the players are ordered by their score and assigned a rank, so I'm using the RANK() function:

SELECT RANK() OVER (ORDER BY [Score] DESC) AS [Score_Rank],
    [Name],
    [Score]
FROM [dbo].[PlayerScores]

So far so good. For the above data, I'll get

Rank | Name | Score
=================
1    | Erin | 33
2    | Ann  | 31
3    | Fred | 29
4    | Dan  | 25
5    | Carl | 24
6    | Bob  | 17
6    | Lou  | 17
8    | Joan | 11

However, when I present this leaderboard to the players, I don't need or want to show them everything - only the players immediately above and below them (there won't be any paged navigation - players only get to see a snapshot of their overall position).

I'm therefore trying to retrieve (n) rows of data surrounding a specific player, such that:

If there are (n) or fewer rows in the table, all rows will be returned.
Where there are at least (n) rows in the table, (n) rows of data will be returned.
There should be (n/2) rows above and below the specified player.
If there aren't (n/2) rows above the specified player, return all the rows above, and enough rows below to make up (n) rows total.
If there aren't (n/2) rows below the specified player, return all the rows below, and enough rows above to make up (n) rows total.

How can I construct my query such that I can always return the minimum number of rows? E.g. for my above dataset and n=5, Erin would see

Rank | Name | Score
=================
1    | Erin | 33
2    | Ann  | 31
3    | Fred | 29
4    | Dan  | 25
5    | Carl | 24

While Dan would see

Rank | Name | Score
=================
2    | Ann  | 31
3    | Fred | 29
4    | Dan  | 25
5    | Carl | 24
6    | Bob  | 17

And Lou would see

Rank | Name | Score
=================
4    | Dan  | 25
5    | Carl | 24
6    | Bob  | 17
6    | Lou  | 17
8    | Joan | 11

I found a partial solution for this using a UNION on two queries (one getting n/2 rows above and one getting n/2 rows below the specified player), but it falls down if the player is at (or near) the top or bottom of the table - the resulting dataset is clipped, and I always want to retrieve a full (n) rows where possible.

I think the solution might have something to do with Window functions, making use of LAG and LEAD, but I honestly can't get my head around the syntax and most of the examples I've found don't care about not returning enough rows total. Thanks!

DVT · Answer 1 · 2016-12-17T19:35:14.120

This will do what you want.

WITH cte AS (
SELECT RANK() OVER (ORDER BY [Score] DESC) AS [Score_Rank],
    ROW_NUMBER() OVER (ORDER BY [Score] DESC) AS [RowNum],
    COUNT(ID) OVER (PARTITION BY (Select NULL)) AS MaxRow,
    [Name],
    [Score],
    [ID]
FROM @playScores
)
SELECT Score_Rank, Name, Score
FROM
    cte
    CROSS APPLY (SELECT RowNum AS AnchorRN FROM cte WHERE ID = @playerID) tmp
WHERE
    (
    RowNum <=
    CASE WHEN tmp.AnchorRN < ((@n)/2) THEN @n
        ELSE tmp.AnchorRN + ((@n)/2) END 
    )
    AND
    (
    RowNum >=
    CASE WHEN tmp.AnchorRN > (MaxRow - (@n)/2) THEN (MaxRow -@n + 1)
    ELSE tmp.AnchorRN - ((@n)/2) END
    );

SELECT *
    , ROW_NUMBER() OVER (ORDER BY Score) AS RowNum
FROM
    @playScores
ORDER BY
    RowNum;

This is the whole answer and test code.

DECLARE @playScores TABLE (
    ID INT
    , Name NVARCHAR(50)
    , Score INT
);

INSERT INTO @playScores (ID, Name, Score)
VALUES
(1  ,' Bob  ', 17),
(2  ,' Carl ', 24),
(3  ,' Ann  ', 31),
(4  ,' Joan ', 11),
(5  ,' Lou  ', 17),
(6  ,' Dan  ', 25),
(7  ,' Erin ', 33),
(8  ,' Fred ', 29);

DECLARE @n INT = 5;
DECLARE @playerID INT =5;

SELECT *
FROM
    @playScores
ORDER BY
    Score DESC;

WITH cte AS (
SELECT RANK() OVER (ORDER BY [Score] DESC) AS [Score_Rank],
    ROW_NUMBER() OVER (ORDER BY [Score] DESC) AS [RowNum],
    COUNT(ID) OVER (PARTITION BY (Select NULL)) AS MaxRow,
    [Name],
    [Score],
    [ID]
FROM @playScores
)
SELECT Score_Rank, Name, Score
FROM
    cte
    CROSS APPLY (SELECT RowNum AS AnchorRN FROM cte WHERE ID = @playerID) tmp
WHERE
    (
    RowNum <=
    CASE WHEN tmp.AnchorRN < ((@n)/2) THEN @n
        ELSE tmp.AnchorRN + ((@n)/2) END 
    )
    AND
    (
    RowNum >=
    CASE WHEN tmp.AnchorRN > (MaxRow - (@n)/2) THEN (MaxRow -@n + 1)
    ELSE tmp.AnchorRN - ((@n)/2) END
    );

SELECT *
    , ROW_NUMBER() OVER (ORDER BY Score) AS RowNum
FROM
    @playScores
ORDER BY
    RowNum;

SELECT *
    , ROW_NUMBER() OVER (ORDER BY Score) AS RowNum
FROM
    @playScores
ORDER BY
    RowNum;

This is returning 6 rows for player 5 (Lou) http://rextester.com/LXWXI90550 — SqlZim, Dec 17 '16 at 19:17
Thanks for the answer - this works now, but I've accepted @SqlZim's answer as I could more or less use the sproc as a drop-in solution to my problem. Still useful :) — Graham Harper, Dec 17 '16 at 19:35

score 1 · Accepted Answer · edited May 23 '17 at 12:08

sql rank vs row number

Two versions of the same procedure, one outputs the result set in order, the second does not.

rextester link to try it out: http://rextester.com/JLQU48329

create table dbo.PlayerScores (Id int, Name nvarchar(64), Score int)
  insert into dbo.PlayerScores (Id, Name, Score) values
   (1,'Bob',17) ,(2,'Carl',24) ,(3,'Ann',31) ,(4,'Joan',11)
  ,(5,'Lou',17) ,(6,'Dan',25) ,(7,'Erin',33) ,(8,'Fred',29);
go
/* ordered resultset */
create procedure dbo.PlayerScores_getMiddle_byId (@PlayerId int, @Results int = 5) as 
begin;
  with cte as (
    select
        Score_Order = row_number() over (order by Score desc)
      , Score_Rank  = rank() over (order by Score desc)
      , Id
      , Name
      , Score
      from dbo.PlayerScores
  )
  select c.Score_Rank, c.Name, c.Score
    from (
      select top (@Results) i.* 
      from cte i 
        cross apply (select Score_Order from cte where Id = @PlayerId) as  x
      order by abs(i.Score_Order-x.Score_Order)
      ) as  c
    order by Score_Rank;
end
go
exec dbo.PlayerScores_getMiddle_byId 7,5; -- Erin
exec dbo.PlayerScores_getMiddle_byId 6,5; --Dan
exec dbo.PlayerScores_getMiddle_byId 5,5; --Lou

go
/* unordered result set */
/* 
create procedure dbo.PlayerScores_getMiddle_byId (@PlayerId int,@Results int = 5) as 
begin;
with cte as (
  select
      Score_Order = row_number() over (order by Score desc)
    , Score_Rank  = rank() over (order by Score desc)
    , Id
    , Name
    , Score
    from dbo.PlayerScores
    )

select top (@Results) c.Score_Rank, c.Name, c.Score
  from cte as c
    cross apply (select 
        Score_Order 
    from cte 
    where Id = @PlayerId) as x
    order by abs(c.Score_Order-x.Score_Order) 

end
--go
exec dbo.PlayerScores_getMiddle_byId 7,5; -- Erin
exec dbo.PlayerScores_getMiddle_byId 6,5; --Dan
exec dbo.PlayerScores_getMiddle_byId 5,5; --Lou
--*/

Charles Bretana · Answer 3 · 2016-12-17T18:59:29.757

or use standard SQL:

with pRank(id, name, rank)
as (Select p.Id, p.Name nam,
      (Select count(*) from players
       where score <= p.score) rnk    
   from players p)
Select p.id, p.nam, p.score,
       n.id, n.nam, n.score
from pRank p join pRank n 
        on n.Rnk between 
               case when p.Rnk < @n/2 then 0 
                    else p.Rnk - @n / 2 end
           and case when p.Rnk < @n/2 then @n 
                    else p.Rnk + @n / 2 end   
order by p.rnk, p.Id, n.rnk

Test:

declare @t table 
(id integer primary key not null,  
  nam varchar(30) not null, score int not null)   
insert @t(id, nam, score)
values
  (1, 'Bob ',17), 
  (2, 'Carl',24),
  (3, 'Ann ',31),
  (4, 'Joan',11),
  (5, 'Lou ',17),
  (6, 'Dan ',25),
  (7, 'Erin',33),
  (8, 'Fred',29)

declare @n int = 4;
with pRank(id, nam, rnk)
as (Select p.Id, p.Nam,
     (Select count(*) from @t
      where score <= p.score) rank    
from @t p)
Select p.id, p.Nam, p.rnk,
     n.id, n.nam, n.rnk
from pRank p join pRank n 
    on n.rnk between 
           case when p.rnk < @n/2 then 0 
                else p.rnk - @n / 2 end
       and case when p.rnk < @n/2 then @n 
                else p.rnk + @n / 2 end
 order by p.rnk, p.id, n.rnk        .

Thanks for the answer @Charles, but I'm afraid I'm still unsure as to how it solves my problem. The query I'm trying to write should return a single set of results, with (n) rows, for a specific player (specified by their ID) - something that I could make into a reusable function by passing those two bits of data as function parameters. If my question didn't communicate that very well, or if I've misunderstood your answer, I'd appreciate some feedback. — Graham Harper, Dec 17 '16 at 19:08

Getting a minimum total number of rows either side of a specific row

3 Answers3