ROW_NUMBER() to be applied only for top N records

Question

I have a scenario to get the zero-indexed value for a result set based on the Id column. I can achieve this by ROW_NUMBER(). But there was a slight change in the expected behavior now.

The expectation is, for the top five records only the IndexValue need to be displayed, for the remaining entries the IndexValue should be NULL.

To achieve this, I have set the IndexValue by ROW_NUMBER(), push into a table variable @PopulateValues, then using UPDATE I get the expected result as shown below:

What I have tried:

-- Actual Table in database
DECLARE @OriginalTable TABLE (Id INT IDENTITY(1, 1), [Name] VARCHAR (255));
INSERT INTO @OriginalTable ([Name]) VALUES 
('Name 01'), ('Name 02'), ('Name 03'), ('Name 04'), ('Name 05'), 
('Name 06'), ('Name 07'), ('Name 08'), ('Name 09'), ('Name 10'), 
('Name 11'), ('Name 12'), ('Name 13'), ('Name 14'), ('Name 15');

-- Table variable for the populate calculation
DECLARE @PopulateValues TABLE (Id INT, [Name] VARCHAR (255), IndexValue INT NULL);
INSERT INTO @PopulateValues (Id, [Name], IndexValue)
SELECT Id, [Name], ROW_NUMBER() OVER (ORDER BY Id) - 1 AS IndexValue
FROM @OriginalTable;

UPDATE @PopulateValues SET IndexValue = NULL WHERE IndexValue >= 5;

SELECT * FROM @PopulateValues;

By this approach, I am able to achieve my expectation, but is there any other way I can apply the ROW_NUMBER() only for TOP N records while SELECT?

Answer 1

You could use a case expression in the query:

SELECT Id, Name, CASE WHEN IndexValue < 5 THEN IndexValue END AS IndexValue
FROM   (SELECT Id, Name, ROW_NUMBER() OVER (ORDER BY Id) - 1 AS IndexValue
        FROM   @OriginalTable) t