September 19, 2019 at 11:03 am
I have a table: Named HighScores
"INSERT HighScores
VALUES ('Bob', 2500, '2 Jan 2013 13:13'),
('Jon', 1500, '2 Jan 2013 13:15'),
('Amy', 3500, '2 Jan 2013 13:18')"
I want to create a stored procedure that would:
The score must show the TOP 5 or so people.
Show your position on the high score board
Show the person in front and below you on the high score board.
If the scores are tied, the person who got it the most recent is shown higher.
Can anybody assist me please?
September 19, 2019 at 11:29 am
something like this might work
DECLARE @rnk INT
SELECT @rnk=
rnk FROM (
SELECT ROW_NUMBER() OVER (ORDER BY score) AS rnk,* FROM highscores
) AS X WHERE NAME='Bob'
SELECT *
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY score) AS rnk,* FROM highscores
) AS X WHERE rnk=BETWEEN (@rnk-1 AND @rnk+1 )
i didn't do the top 5 as i don't have your table structure, but i'm sure you could modify it
MVDBA
September 19, 2019 at 2:52 pm
take the brackets out of the between statement - I put them in by accident
MVDBA
September 19, 2019 at 2:55 pm
This code has the 3 ranking methods (row_num, row_rank, and dense_row_rank) and joins to itself twice to get the next and previous list members. The ORDER BY for the rankings is both score and entry_dt.
drop table if exists test_high_scores;
go
create table test_high_scores(
player_namevarchar(10) not null,
scoreint not null,
entry_dtdatetime not null);
go
insert test_high_scores values
('Bob', 2500, '2 jan 2013 13:13'),
('Bob', 2500, '2 jan 2013 13:13'),
('Jon', 1500, '2 jan 2013 13:15'),
('Amy', 1500, '2 jan 2013 13:15'),
('Amy', 3500, '2 jan 2013 13:18');
go
with ranking_cte as(
select
player_name,
score,
entry_dt,
row_number() over(order by score desc, entry_dt desc) as row_num,
rank() over(order by score desc, entry_dt desc) as row_rank,
dense_rank() over(order by score desc, entry_dt desc) as dense_row_rank
from
test_high_scores)
select
rc.*, rc_lag.*, rc_nxt.*
from
ranking_cte rc
left join
ranking_cte rc_lag on rc.row_num=rc_lag.row_num+1
left join
ranking_cte rc_nxt on rc.row_num=rc_nxt.row_num-1
order by
rc.row_num asc;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
September 19, 2019 at 3:06 pm
Thank You.
Can i define player_name as an index?
How do i add highscores to the table using pr_PutHighScoreList(stored procedure)
You can only appear on the high score list once, and only your highest
score must be stored.
Also need to record the movement in the highscore table, when
players move up in position.
September 19, 2019 at 3:06 pm
to the original poster
it might be wise to google the difference between row_number, rank and dense rank - scdecade was right to point out these options, but you need to understand which one to choose.
although i'm not keen on the joins in that solution, but if it's a small table then no problem (you won't notice the difference) - but it is a good script based on what you asked for
MVDBA
September 19, 2019 at 3:11 pm
Thank You.
Can i define player_name as an index?
How do i add highscores to the table using pr_PutHighScoreList(stored procedure)
You can only appear on the high score list once, and only your highest
score must be stored.
Also need to record the movement in the highscore table, when
players move up in position.
if your score can only appear once then you can use the merge command and link based on score and maybe date.
or use something like
if exists(select * from scores where score <@currentscore and user='bob')
begin
delete from scores where.....
insert into scores .........
end
but my advice is to look at the merge command
MVDBA
September 19, 2019 at 3:13 pm
and yes you can assign and index to player name - but get an execution plan first and see what it really needs
create index IX_scores on highscores(playername) would be approximately the syntax
MVDBA
September 20, 2019 at 2:57 pm
I guess this procedure will also follow the above principle?
Design a stored proc that will add an entry to HelpDeskCalls.
For new entries, RefID must be sequential. For updates to existing tasks, the TaskID must be sequential.
Only the users that are allowed to log entries can do so.
Calls are closed, but can be reopened if the user not satisfied with the outcome.
Following reports:
The number of calls logged, and the average amount of tasks it took to complete.
The person that logged the most calls.
The person that fixed the most calls.
The person that had the most calls reopened.
September 20, 2019 at 3:02 pm
are you doing this for a job interview/school exam?
MVDBA
September 20, 2019 at 3:47 pm
When requesting help with a problem like this, you need to provide CONSUMABLE DATA and expected results. Text tables are not CONSUMABLE. PICTURES are not DATA. This is how you provide consumable data.
DROP TABLE IF EXISTS #high_scores;
CREATE TABLE #high_scores(
player_nameVARCHAR(10) NOT NULL,
scoreINT NOT NULL,
entry_dtDATETIME NOT NULL);
--CREATE INDEX PK_High_Scores ON #high_scores(score DESC, entry_dt DESC, player_name);
INSERT #high_scores VALUES
('Bob', 2500, '2 jan 2013 13:13'),
('Ed', 2500, '2 jan 2013 13:12'),
('Jon', 1500, '2 jan 2013 13:15'),
('Amy', 1500, '2 jan 2013 13:14'),
('Rob', 2500, '2 jan 2013 13:13'),
('Al', 2000, '2 jan 2014 14:12'),
('Chris', 1500, '2 jan 2014 14:15'),
('Don', 1500, '2 jan 2014 14:14'),
('Geo', 4500, '3 jan 4013 13:13'),
('Hal', 4500, '3 jan 4013 13:12'),
('Ira', 3000, '3 jan 4013 13:30'),
('Jo', 3000, '3 jan 4013 13:14'),
('Kurt', 4500, '3 jan 4013 13:13'),
('Lily', 4000, '3 jan 4014 14:12'),
('Mo', 3000, '3 jan 4014 14:30'),
('Nina', 3500, '2 jan 2013 13:18');
GO
Also note that you should provide enough data to actually test your conditions. You need at least 8 records (top 5 plus a window of 3 more records) in your sample. You only provided 3.
The following approach only requires one scan of the table. It will give the top 5 players with ties and also show the person requested and the people above and below them in the ranking. In this example, that is positions 11-13. If you don't want ties, use ROW_NUMBER() instead of RANK(). You do not want to use DENSE_RANK() here.
DECLARE @player VARCHAR(10) = 'Al';
WITH ranked_players AS
(
SELECT
hs.player_name
,hs.score
,hs.entry_dt
,RANK() OVER(ORDER BY hs.score DESC, hs.entry_dt DESC) AS player_rank
,MAX(CASE WHEN hs.player_name = @player THEN 1 ELSE 0 END) OVER(ORDER BY hs.score DESC, hs.entry_dt DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS in_window
FROM #high_scores AS hs
)
SELECT *
FROM ranked_players AS rp
WHERE rp.player_rank <= 5
OR rp.in_window = 1
ORDER BY rp.player_rank, rp.entry_dt;
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 20, 2019 at 3:57 pm
The ORDER BY entr_dt I think should be ascending. Earlier is better?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
September 20, 2019 at 4:14 pm
Thank You.
Can i define player_name as an index?
How do i add highscores to the table using pr_PutHighScoreList(stored procedure)
You can only appear on the high score list once, and only your highest
score must be stored.
Also need to record the movement in the highscore table, when
players move up in position.
Pretty much everything you're listing here is not really necessary imo. Just you only need to keep a good list of all scores in 1 table. Your proc is prefixed with "Put..." implying you going to update a score list. It shouldn't even be necessary to update the list of all scores. Just insert and delete. Everything else is queries.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
September 20, 2019 at 4:20 pm
The ORDER BY entr_dt I think should be ascending. Earlier is better?
If the scores are tied, the person who got it the most recent is shown higher.
I assume that higher means higher in the ranking or closer to 1, so it should be descending.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply