TopN Stored Procedure

One of the websites that I work on has some learning based games and the web designer would like to display the top 5 or 10 games played. A nice idea. I concluded that the number of games played that could be displayed should not be limited to 5 or 10 but could be any number, e.g. 8, 7 or 83.

I started out using the TOP keyword as part of the SELECT statement:

SELECT TOP 5 description, hit_count

view raw example1.sql hosted with ❤ by GitHub

The problem here is that T-SQL will not allow a variable to be used within a SELECT statement. The common solution to this is to use dynamic SQL which is not pretty to look at and causes a performance hit on the server. A better solution is to use a seeded temporary table.

First create a temporary table with a seeded ID (auto identity) column along with the columns required for the result. Then insert the results from the main query into the temporary table. Remember to set the sort order you require when inserting the rows. Finally, select the results from the temporary table where the ID is not greater than the number of results you need e.g. the top 10 games

Here is my working example:

	CREATE PROCEDURE dbo.media_TopNCategories
	(
	@category varchar(300),
	@TopN int
	)
	as
	CREATE TABLE #top_results (
	id [int] IDENTITY(1, 1),
	description [varchar] (300),
	hit_count [int]
	)
	INSERT #top_results (description, hit_count)
	SELECT m.description, m.hit_count
	FROM dbo.media m
	JOIN dbo.media_categories mc
	on mc.id = m.category_id
	WHERE mc.description = @category
	ORDER BY m.hit_count DESC
	SELECT id, description, hit_count
	FROM #top_results
	WHERE id < = @TopN

view raw example2.sql hosted with ❤ by GitHub

A simple solution to a common problem. Temporary tables are a very useful tool for transforming the shape data and often remove the need for dynamic SQL or cursors.