ROW_NUMBER() to be applied only for top N records

user_2451726 0
Viewed 65

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?

row-number select sql-server sql
3 Answers

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
user_24227760

You could use a CASE statement along with a subquery like so:

INSERT INTO @PopulateValues (Id, [Name], IndexValue)
SELECT x.[Id], x.[Name], CASE WHEN x.IndexValue < 5 THEN x.IndexValue ELSE NULL END AS [IndexValue]
FROM
(
SELECT Id, [Name], ROW_NUMBER() OVER (ORDER BY Id) - 1 AS IndexValue
FROM @OriginalTable;
) AS x
user_48301960

As I found one more possible way using Logical Function - IIF (starting with SQL Server 2012), so the query will be:

SELECT Id, [Name], IIF(IndexValue < 5, IndexValue, NULL) AS IndexValue 
FROM  ( SELECT Id, Name, ROW_NUMBER() OVER (ORDER BY Id) - 1 AS IndexValue
        FROM @OriginalTable ) Q
user_24517260
Related