Earlier this week some colleague had been assigned a maintenance task and asked me how I would solve it. Every customer is permitted to have an amount of publications. All excess publications should be removed from the system (only the n most recent ones are permitted to remain on the system).

Here is an example of the Customer table:

CREATE TABLE [dbo].[Customer](
	[CustomerId] [int] IDENTITY(1,1) NOT NULL,
	[CustomerName] [nvarchar](50) NOT NULL,
	[PermittedPublications] [int] NOT NULL
);

INSERT INTO [dbo].[Customer] 
			([CustomerName], [PermittedPublications]) 
VALUES	
			('timvw', 2),
			('mike', 3);

An example of the customer publications table:

CREATE TABLE [dbo].[Publication](
	[PublicationId] [int] IDENTITY(1,1) NOT NULL,
	[CustomerId] [int] NOT NULL,
	[PublicationName] [nvarchar](50) NOT NULL,
	[PublicationTime] [datetime2] NOT NULL
);
                         
INSERT INTO [dbo].[Publication] 
			([CustomerId], [PublicationName],[PublicationTime])
VALUES      
			((SELECT [CustomerId] FROM [dbo].[Customer] WHERE [CustomerName] = 'timvw'), 'tim pub1', SYSUTCDATETIME()),
			((SELECT [CustomerId] FROM [dbo].[Customer] WHERE [CustomerName] = 'timvw'), 'tim pub2', SYSUTCDATETIME()),
			((SELECT [CustomerId] FROM [dbo].[Customer] WHERE [CustomerName] = 'timvw'), 'tim pub3', SYSUTCDATETIME()),
			((SELECT [CustomerId] FROM [dbo].[Customer] WHERE [CustomerName] = 'timvw'), 'tim pub4', SYSUTCDATETIME()),
			((SELECT [CustomerId] FROM [dbo].[Customer] WHERE [CustomerName] = 'mike'), 'mike pub1', SYSUTCDATETIME()),
			((SELECT [CustomerId] FROM [dbo].[Customer] WHERE [CustomerName] = 'mike'), 'mike pub2', SYSUTCDATETIME());

My colleague was keen on using some cursor logic, but I demonstrated him how a set-based alternative:

		
WITH [RankedPublication] AS (
	SELECT [CustomerId]
	      ,[PublicationId]
	      ,[PublicationName]
	      ,[PublicationTime]
	      ,ROW_NUMBER() OVER(PARTITION BY [CustomerId] ORDER BY [PublicationTime]) AS [PublicationRank]
	FROM [dbo].[Publication]
), [ExcessPublication] AS (
	SELECT [PublicationId]
	FROM [RankedPublication]
	INNER JOIN [dbo].[Customer] ON [Customer].[CustomerId] = [RankedPublication].[CustomerId]
	WHERE [PublicationRank] > [Customer].[PermittedPublications]
)
	DELETE FROM [dbo].[Publication]
	WHERE [PublicationId] IN (SELECT [PublicationId] FROM [ExcessPublication]);