How to get Leader Boards (Rank) detail.

• s.chandrahasan

  SSC Eights!

  Points: 858

  More actions

  August 30, 2017 at 10:49 pm

  #332451

  CREATE TABLE [dbo].[leaderboardvalues](
    [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [userid] [int] NULL,
    [typeid] [int] NULL,
    [value] [int] NULL)

  INSERT [dbo].[leaderboardvalues] ([userid], [typeid], [value]) VALUES (2, 1, 12)
INSERT [dbo].[leaderboardvalues] ([userid], [typeid], [value]) VALUES (3, 1, 23)
INSERT [dbo].[leaderboardvalues] ([userid], [typeid], [value]) VALUES (4, 2, 35)
INSERT [dbo].[leaderboardvalues] ([userid], [typeid], [value]) VALUES (5, 1, 34)
INSERT [dbo].[leaderboardvalues] ([userid], [typeid], [value]) VALUES (6, 2, 12)
INSERT [dbo].[leaderboardvalues] ([userid], [typeid], [value]) VALUES (7, 1, 34)
INSERT [dbo].[leaderboardvalues] ([userid], [typeid], [value]) VALUES (8, 1, 67)
INSERT [dbo].[leaderboardvalues] ([userid], [typeid], [value]) VALUES (9, 2, 45)
INSERT [dbo].[leaderboardvalues] ([userid], [typeid], [value]) VALUES (10, 1, 23)
INSERT [dbo].[leaderboardvalues] ([userid], [typeid], [value]) VALUES (11, 2, 86)
INSERT [dbo].[leaderboardvalues] ([userid], [typeid], [value]) VALUES (12, 1, 78)
INSERT [dbo].[leaderboardvalues] ([userid], [typeid], [value]) VALUES (13, 2, 8)
INSERT [dbo].[leaderboardvalues] ([userid], [typeid], [value]) VALUES (14, 1, 98)
INSERT [dbo].[leaderboardvalues] ([userid], [typeid], [value]) VALUES (15, 2, 11)
INSERT [dbo].[leaderboardvalues] ([userid], [typeid], [value]) VALUES (16, 1, 12)
INSERT [dbo].[leaderboardvalues] ([userid], [typeid], [value]) VALUES (17, 2, 35)
INSERT [dbo].[leaderboardvalues] ([userid], [typeid], [value]) VALUES (18, 1, 10)

  Adding Rank
  select ROW_NUMBER() OVER (order by Value desc) Sequence_no,userid,Value,DENSE_RANK() over (order by Value desc) as Rank
from leaderboardvalues where typeid=1

  Through this above query I can generate rank for all the rows ,  But I want result for specific user with above 3 rank and below 3 rank details.
  For example
  If I want to take Rank for UserID 3 , then I need result like 

  
ID     UserID     Value    Rank
3       8          67          3
4       5          34          4
5       7          34          4
6     3        23       5
7      10          23          5
8      16          12          6
9       2          12          6

  UserID  should be  in Middle and above 3 rank details and below 3 rank details need to fetch total 7 records not all the rows.

  Kindly advise any best approach for this .

• David Burrows

  SSC Guru

  Points: 65142

  More actions

  August 31, 2017 at 3:27 am

  #1957598

  Not sure if the best solution
  
WITH cte AS (
 SELECT ROW_NUMBER() OVER (order by Value desc) Sequence_no,
   userid,
   Value,
   DENSE_RANK() over (order by Value desc) as Rank
 FROM dbo.leaderboardvalues
 WHERE typeid=1
 ),
 cte2 AS (
  SELECT Sequence_no
  FROM cte
  WHERE userid = 3
  )
SELECT cte.Sequence_no,cte.userid,cte.Value,cte.Rank
FROM cte2
  JOIN cte ON cte.Sequence_no BETWEEN cte2.Sequence_no - 3
   AND cte2.Sequence_no + 3
ORDER BY Sequence_no ASC


  Far away is close at hand in the images of elsewhere.
  Anon.

• Thomas Rushton

  SSC-Insane

  Points: 22649

  More actions

  August 31, 2017 at 3:35 am

  #1957600

  You could try something like this:

  WITH OrderedRanked
AS (SELECT ROW_NUMBER() OVER (ORDER BY value DESC) Sequence_no,
    userid,
    value,
    DENSE_RANK() OVER (ORDER BY value DESC) AS Rank
  FROM leaderboardvalues
  WHERE typeid = 1
 )
SELECT *
FROM OrderedRanked
WHERE ABS( OrderedRanked.Sequence_no -
     (
      SELECT Sequence_no FROM OrderedRanked WHERE userid = 3
     )
   ) <= 3
ORDER BY OrderedRanked.Sequence_no ASC;

  This uses a CTE (common table expression) to wrap up your main select, then extracts the bits that are actually any interest by comparing the sequence number of each row with that of the sequence number of the row for userid 3.

  Hope it helps.

  --edited to add ORDER BY.

  Thomas Rushton
  blog: https://thelonedba.wordpress.com

• drew.allen

  SSC Guru

  Points: 76987

  More actions

  September 1, 2017 at 12:48 pm

  #1957887

  This only takes one scan of the table whereas the other two solutions require two.

  ;
WITH leaderboard AS
(
    select
        ROW_NUMBER() OVER (order by Value desc) Sequence_no
    ,    userid
    ,    Value
    ,    DENSE_RANK() over (order by Value desc) as Rank
    ,    MAX(CASE WHEN userid = 3 THEN 1 ELSE 0 END) OVER(ORDER BY Value DESC ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS in_frame
    from #leaderboardvalues where typeid=1
)
SELECT Sequence_no, userid, value, [Rank]
FROM leaderboard
WHERE in_frame = 1
;


  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA