SQL - SELECT TOP n or SELECT TOP Random n Rows From a Table For Each Category or Group
You may need a sql query that will select top n records or random n records for each category in a table. The t-sql query that will solve this problem may be difficult for first timers, especially if you are working on MS SQL Server 2000. Now, with the t-sql enhancements in Microsoft SQL Server 2005 the problem of selecting a definite number of records grouped or categorized according to a column is easier to create.
Let's define the problem once more to make you easy to imagine in your mind.
Assume that you have articles categorized by their topics. Say, articles may be in categories T-SQL, SSAS, SSIS, SSRS, .NET Framework, ASP.NET, VB.NET, C#, VISTA etc.
You want a t-sql query that will display random 3 records from each available category in the main page of your web site.
CREATE TABLE ArticleCategories (
ArticleCategoryId smallint IDENTITY(1,1) NOT NULL,
Description nvarchar(50) NOT NULL,
Active bit NOT NULL
)
GO
CREATE TABLE Articles (
ArticleId int IDENTITY(1,1) NOT NULL,
Title nvarchar(250) NOT NULL,
ArticleCategoryId smallint NOT NULL,
Text nvarchar(max) NOT NULL,
Active bit NOT NULL
)
GO
INSERT INTO ArticleCategories SELECT N'T-SQL', 1
INSERT INTO ArticleCategories SELECT N'SSRS', 1
INSERT INTO ArticleCategories SELECT N'ASP.NET', 1
INSERT INTO ArticleCategories SELECT N'VB.NET', 1
INSERT INTO Articles SELECT N'How to delete duplicate records in a table where no primary key exists', 1, N'', 1
INSERT INTO Articles SELECT N'How to create SQL Server cursor and sample sql cursor code', 1, N'', 1
INSERT INTO Articles SELECT N'How to find the first day of a month and the last day of a month?', 1, N'', 1
INSERT INTO Articles SELECT N'Reporting Services Client-Side Printing & Silent Deployment of RSClientPrint.cab file', 2, N'', 1
INSERT INTO Articles SELECT N'How to Build Your First Report In MS SQL Server 2005 Reporting Services', 2, N'', 1
INSERT INTO Articles SELECT N'How to Add Auto Number Records In Reporting Services by Using RowNumber Function', 2, N'', 1
INSERT INTO Articles SELECT N'How to use ReportViewer Control in Microsoft Visual Studio 2005', 3, N'', 1
INSERT INTO Articles SELECT N'Localization Sample ASP.NET Web Application', 3, N'', 1
INSERT INTO Articles SELECT N'Using the ASP.NET 2.0 Menu Control with Site Maps', 3, N'', 1
INSERT INTO Articles SELECT N'Conditional Statements in VB.NET', 4, N'', 1
INSERT INTO Articles SELECT N'How to check that a unique instance of a process is running', 4, N'', 1
INSERT INTO Articles SELECT N'Format Minute to Hours in VB.NET', 4, N'', 1
After inserting the above records as sample into the Article Categories and Articles by running the above sql code, we are ready for running the first t-sql script.
SELECT
AC.ArticleCategoryId,
AC.Description,
A.ArticleId,
A.Title
FROM ArticleCategories AC (NoLock)
LEFT JOIN Articles A (NoLock) ON A.ArticleCategoryId = AC.ArticleCategoryId
WHERE A.ArticleId IN (
SELECT TOP 2 ArticleId
FROM Articles A (NoLock)
WHERE A.ArticleCategoryId = AC.ArticleCategoryId
ORDER BY A.ArticleId DESC
)
ORDER BY
AC.ArticleCategoryId,
A.ArticleId DESC
What is important about the above t-sql select command is that it can also run on MS SQL Server 2000 successfully.
If you are running SQL Server 2005 or SQL Server 2008 as your database, you can try the following sql select statements also.
Here in this sql select top query, we are using the ROW_NUMBER() OVER (PARTITION BY columnname ORDER BY DESC) to get the list of articles with a row number grouped according to the column values, in our sample ArticleCategoryId. This creates a new numbering starting from 1 for each article category.
SELECT
AC.ArticleCategoryId,
AC.Description,
A.ArticleId,
A.Title
FROM ArticleCategories AC (NoLock)
INNER JOIN (
SELECT
ROW_NUMBER() OVER(PARTITION BY A.ArticleCategoryId ORDER BY A.ArticleId DESC) AS RowNumber,
A.ArticleCategoryId,
A.ArticleId,
A.Title
FROM Articles A (NoLock)
) A ON A.ArticleCategoryId = AC.ArticleCategoryId
WHERE A.RowNumber < 3
An other method of selecting records belonging to different groups or categories can be implemented by using the CROSS APPLY join shown as in the below t-sql select statement.
SELECT
AC.ArticleCategoryId,
AC.Description,
A.ArticleId,
A.Title
FROM ArticleCategories AC (NoLock)
CROSS APPLY (
SELECT TOP 2 ArticleId, Title
FROM Articles A (NoLock)
WHERE A.ArticleCategoryId = AC.ArticleCategoryId
ORDER BY A.ArticleId DESC
) A
ORDER BY A.ArticleId DESC
I think you have noticed that till now we have selected our articles or rows according to an order of column values descending or ascending.
We can further alter the select statements in order to select random records from each group of record by using the ORDER BY CHECKSUM(NEWID())
Here is the updated scripts of sql which fetch random n rows from each category in a table.
DECLARE @n int
SET @n = 2
SELECT
AC.ArticleCategoryId,
AC.Description,
A.ArticleId,
A.Title
FROM ArticleCategories AC (NoLock)
CROSS APPLY (
SELECT TOP(@n) ArticleId, Title
FROM Articles A (NoLock)
WHERE A.ArticleCategoryId = AC.ArticleCategoryId
ORDER BY CHECKSUM(NEWID())
) A
GO
DECLARE @n int
SET @n = 3
SELECT
AC.ArticleCategoryId,
AC.Description,
A.ArticleId,
A.Title
FROM ArticleCategories AC (NoLock)
INNER JOIN (
SELECT
ROW_NUMBER() OVER(PARTITION BY A.ArticleCategoryId ORDER BY CHECKSUM(NEWID())) AS RowNumber,
A.ArticleCategoryId,
A.ArticleId,
A.Title
FROM Articles A (NoLock)
) A ON A.ArticleCategoryId = AC.ArticleCategoryId
WHERE A.RowNumber < @n
GO