August 30, 2017 at 10:49 pm
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 Rankselect 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 .
August 31, 2017 at 3:27 am
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.
August 31, 2017 at 3:35 am
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
September 1, 2017 at 12:48 pm
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply