SQL COUNT() and ROW_NUMBER() Function with PARTITION BY for 1 of n Items
This SQL tutorial demonstrates usage of SQL COUNT() and SQL ROW_NUMBER() function to select a row with its order number and total number of rows in a group in the format "1 of n" items, like first page of a total 3 pages, 1/3, 1 of 3, etc.
In order to get the order number, SQL Window function Row_Number() which is introduced with SQL Server 2005 SQL development enhancements is used. For total number in the desired category SQL aggregate function COUNT() is used with PARTITION BY clause which is also available since SQL Server 2005.
Partition By enables a grouping according to the list of columns given in the Partition By clause. SQL Window functions like Row_Number(), Rank(), Dense_Rank(), Tile(), NTile() and aggregate functions like SUM(), COUNT(), AVEGARE(), MAX(), MIN(), etc provides data valid within that partition. This provides SQL developers code less sql lines without the use of temporary tables and better performance with build-in grouping and partitioning support by SQL Server engine.
We will now create a SQL SELECT statement which brings an image data and its number within total number of images in the related gallery. You can expect a result like "1 of 2 images", "1 / 3", "2 of total 5", etc.
Let's create our sample database tables and populate them with sample data. SQL developers can execute below script which creates Gallery table and Images table. Images table has a foreign key column GalleryId (unfortunately I did not create the foreign key constraint here) which is a reference to Gallery entity.
CREATE TABLE Gallery (
Id int identity(1,1),
[Description] nvarchar(1000)
)
insert into Gallery select 'Database'
insert into Gallery select 'SQL Server'
insert into Gallery select 'Business Intelligence'
CREATE TABLE Images (
ImageId int identity(1,1),
GalleryId int,
[Name] nvarchar(1000),
Path varchar(1000)
)
insert into Images select 1, N'Database Design', '/database-design.png'
insert into Images select 2, N'SQL Server 2014', '/sqlserver2014.png'
insert into Images select 3, N'SSRS', '/ssrs.png'
insert into Images select 2, N'In-memory OLTP', '/in-memory-oltp.jpg'
insert into Images select 1, N'What is RDBMS?', '/rdbms.png'
insert into Images select 1, N'OLAP vs OLTP', '/olap-oltp.jpg'
Now we will create a single SQL query with a SELECT statement but will be using SQL Window function ROW_NUMBER() and SQL Aggregate function COUNT() with PARTITION BY enhancement.
Row_Number() function will get the sort order of the related data row within in its category defined in the Partition By clause
SQL Count() function will return the number of rows (or images) within the grouped set of images based on gallery (galleryid) with the extension Partition By clause.
select
ImageId,
[Name] as [Image],
Path,
[Description] as Gallery,
ROW_NUMBER() over (partition by GalleryId order by ImageId) rn,
COUNT(*) OVER (partition by GalleryId) cnt,
[n of Total m images] =
cast( (ROW_NUMBER() over (partition by GalleryId order by ImageId)) as varchar(5))
+ ' of ' +
cast( (COUNT(*) OVER (partition by GalleryId)) as varchar(5)) + ' images'
from Images i
inner join Gallery g on g.Id = i.GalleryId
Here is the output of the above Transact-SQL Select statement.
Coding with using the new sql functions and enhancements eanbles developers to get the desired values with less code.
For example, in this senario the developers had to use SubSelect statements for the number of images in each gallery. Or at least they have to store the count of images in each gallery into a temp table and join this temp table with Images table in the Select statement for a similar result.
List items using SQL query with their numbers in a group in the "n of m" format