Ranking ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Ranking ?

Author

Topic

jacisme
Starting Member

34 Posts

Posted - 2007-10-13 : 14:09:01

Hello everyone, thank you for your help.

I am trying to build a query that will rank game titles based on the number of users that have visited a specific game.

Example: Halo 3 Rank: 7 of 41005

I use a stored procedure that inserts a game's id into a table every time that game's page is visited. I then count the number of times the game's id are listed to provide the days, years, all time most popular games on my site. In addition to that I want to display any given game's current ranking. The table is very large with, currently, over 5 million game ids.

db_log_game
============
GameID
LogDate

Where GameID = 505246

GameID Rank 7 of 41005

Thank you again for your help, please let me know if you need more info.

JAC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-13 : 14:13:22

SELECT GameID
FROM db_log_game
GROUP BY GameID
ORDER BY COUNT(*) DESC

And the publish the RANKING at client end.

E 12°55'05.25"
N 56°04'39.16"

jacisme
Starting Member

34 Posts

Posted - 2007-10-13 : 15:10:58

Thanks Peso, but I may have not explained myself properly. The results I need would be more like:

WHERE GameID=505246

GameID====Rank
505246====7

Which would be the rank 7 of 41005 total games.

Thanks again,
JAC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-14 : 03:24:59

[code]CREATE PROCEDURE dbo.uspGetRank
(
@GameID INT
)
AS

SET NOCOUNT ON

SELECT GameID, IDENTITY(INT, 1, 1) AS Rank
INTO #Temp
FROM db_log_game
~~INTO #Temp~~
GROUP BY GameID
ORDER BY COUNT(*) DESC

SELECT GameID, Rank
FROM #Temp
WHERE GameID = @GameID

DELETE #Temp[/code]

EXEC dbo.uspGetRank 505246

E 12°55'05.25"
N 56°04'39.16"

jacisme
Starting Member

34 Posts

Posted - 2007-10-14 : 12:30:53

Thanks again Peso, but I receive the following error:

Server: Msg 177, Level 15, State 1, Procedure uspGetRank, Line 10
The IDENTITY function can only be used when the SELECT statement has an INTO clause.

jacisme
Starting Member

34 Posts

Posted - 2007-10-15 : 02:05:37

My apologies, I moved INTO above FROM and it worked! Thank you Peso!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources