Tag Archive for: row number

Passing a Variable to an IN List

/7 Comments/in /by

Every once in a while there is a need to do something like this:

SELECT person_id, person_name
FROM MyUsers
WHERE person_id IN (@search_list);

And @search_list contains some form of a delimited list. However, this is not a supported syntax and will fail. Here is one solution to this problem:

-- Create the test table
CREATE TABLE MyUsers (
 person_id INT PRIMARY KEY,
 person_name VARCHAR(35));
 
-- Insert sample data
INSERT INTO MyUsers VALUES (1327, 'Joe');
INSERT INTO MyUsers VALUES (1342, 'John F.');
INSERT INTO MyUsers VALUES (1411, 'Mary');
INSERT INTO MyUsers VALUES (1345, 'Nancy');
INSERT INTO MyUsers VALUES (1366, 'Greg');
INSERT INTO MyUsers VALUES (1367, 'Jeff');
INSERT INTO MyUsers VALUES (1368, 'Chris');
INSERT INTO MyUsers VALUES (1369, 'John M.');
INSERT INTO MyUsers VALUES (1370, 'Peggy');
INSERT INTO MyUsers VALUES (1371, 'Samuel');
INSERT INTO MyUsers VALUES (1372, 'Tony');
INSERT INTO MyUsers VALUES (1373, 'Lisa');
INSERT INTO MyUsers VALUES (1374, 'Tom');
INSERT INTO MyUsers VALUES (1375, 'Dave');
INSERT INTO MyUsers VALUES (1376, 'Peter');
INSERT INTO MyUsers VALUES (1377, 'Jason');
INSERT INTO MyUsers VALUES (1378, 'Justin');
INSERT INTO MyUsers VALUES (1379, 'Oscar');
 
DECLARE @search_list VARCHAR(100);
 
DECLARE @delimiter CHAR(1);
 
SELECT @search_list = '1327,1342,1411',
       @delimiter = ',';
 
-- Get the users based on the delimited variable list
SELECT person_id, person_name 
FROM MyUsers
WHERE person_id IN 
     (SELECT SUBSTRING(string, 2, CHARINDEX(@delimiter, string, 2) - 2)
      FROM (SELECT SUBSTRING(list, n, LEN(list))
            FROM (SELECT @delimiter + @search_list + @delimiter) AS L(list),
                 (SELECT ROW_NUMBER() OVER (ORDER BY person_id)
                  FROM MyUsers) AS Nums(n)
            WHERE n <= LEN(list)) AS D(string) 
      WHERE LEN(string) > 1
        AND SUBSTRING(string, 1, 1) = @delimiter)
ORDER BY person_id;

-- Results

person_id person_name
-------- ------------------------------
1327     Joe
1342     John F.
1411     Mary

Notes:

– This algorithm is based on walking the delimited string (could be in a table column or a variable) and parsing it into a table format that can feed the IN list.

– The use of ROW_NUMBER assumes you are using SQL Server 2005. However, you can accomplish the same if you have SQL Server 2000. What this subquery does (SELECT ROW_NUMBER() OVER (ORDER BY person_id) FROM MyUsers) is simply generating a table with numbers. In SQL Server 2000 you can create a temp table and generate sequential numbers. The only requirement is that you have more numbers than the length of the string to parse (in this case the length of ‘1327,1342,1411’). These numbers are used as an index in the walk process.

Row Number in SQL Server

/7 Comments/in /by

Every once in a while there is a need to pull the rows with a row number. Here are a few solutions that I have seen to work well.

Below is a sample table with employees that has employee name and employee address columns:

CREATE TABLE Employees (
 employee_name VARCHAR(50) PRIMARY KEY,
 employee_address VARCHAR(100));
 
INSERT INTO Employees (employee_name, employee_address)
VALUES ('Blake Anderson', '2048 River View Rd.');
INSERT INTO Employees (employee_name, employee_address)
VALUES ('Ana Williams', '9055 East Blvd.');
INSERT INTO Employees (employee_name, employee_address)
VALUES ('Robert Schmidt', '3400 Windsor Street');
INSERT INTO Employees (employee_name, employee_address)
VALUES ('Sarah Reese', '1045 Coral Rd.');

SQL Server 2000 and SQL Server 2005

Using an IDENTITY column and a temporary table

This solution is based on creating a temporary table with IDENTITY column used to provide a row number. This approach provides very good performance. Here are the steps:

-- Create the temp table
CREATE TABLE #EmployeeRowNumber (
 rn INT IDENTITY (1, 1),
 employee_name VARCHAR(50),
 employee_address VARCHAR(100));
 
-- Generate the row number
-- To achieve an ordered list the names are sorted
INSERT #EmployeeRowNumber (employee_name, employee_address)
SELECT employee_name, employee_address
FROM Employees
ORDER BY employee_name;
 
-- Select the row number
SELECT employee_name, employee_address, rn
FROM #EmployeeRowNumber
ORDER BY rn;

  
-- Results

employee_name    employee_address         rn
---------------- ------------------------ -----------
Ana Williams     9055 East Blvd.          1
Blake Anderson   2048 River View Rd.      2
Robert Schmidt   3400 Windsor Street      3
Sarah Reese      1045 Coral Rd.           4

Using a subquery to count the number of rows

This solution is based on using a subquery on a unique column (or combination of columns) to count the number of rows. Here is how it looks with the sample data provided above:

SELECT employee_name, employee_address,
      (SELECT COUNT(*) 
       FROM Employees AS E2
       WHERE E2.employee_name <= E1.employee_name) AS rn  
FROM Employees AS E1  
ORDER BY employee_name;

If the values in the column are not unique then duplicate row numbers will be generated. That can be resolved by adding a tiebreaker column that will guarantee the uniqueness. This approach is a slower method than using an IDENTITY column and a temporary table. Since it will incur (n + n2) /2 row scans it may not be practical to use on a large table.

SQL Server 2005

Using the ROW_NUMBER function

In SQL Server 2005 the new function ROW_NUMBER provides the fastest approach to solve the problem:

SELECT employee_name, employee_address,
       ROW_NUMBER() OVER(ORDER BY employee_name) AS rn
FROM Employees
ORDER BY employee_name;

Additional resources:

How to dynamically number rows in a SELECT Transact-SQL statement: http://support.microsoft.com/kb/186133

SQL Server 2005 Ranking Functions: http://msdn2.microsoft.com/en-us/library/ms189798.aspx

Book: “Inside Microsoft SQL Server 2005: T-SQL Querying” by Itzik Ben-Gan, Lubor Kollar and Dejan Sarka