asked on

How can I fill in field for some records with sequential numbers in MSSQL?

I am trying to write a simple query in MSSQL to fill a field in a database with sequential numbers, and I need some help.

The table is QA and it has these important fields:

QuestionID - the "identify" field

Question - I want to filter and only include rcords if the length of this field is greater than 3 (basically non-blank)

QuestionNum - this will contain the new sequential numbers

I looked online, and I have found some solutions, but I am having trouble putting them together (e.g. I found one solution with a "where" clause, but that was only a "select" statement, and not an "update" statement).

Thank you for your help.

Paul MacDonald

SQL Server has an auto-incrementing integer field that may be what you want. Because it's auto-incrementing, you don't have to write code to keep track of it.

Lev Seltzer

ASKER

I already have such a field. I have 4200 records, of which 3800+ need to get numbers. So adding a new auto-increment field will probably just create a new field with the same numbers as the existing auto-increment field.

Instead, I want a new field such that if records 1, 2, and 4 are desired records, these will be renumbered to 1, 2, 3, and the previous record #3 will not get any number in the new field.

Valliappan AN

You may try ROW_NUMBER():

SELECT QuestionID , row_number() over (order by QuestionID ) As QuestionNum FROM [QuestionsTable]

Open in new window

ASKER CERTIFIED SOLUTION

Valliappan AN

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lev Seltzer

ASKER

Perfect. This worked exactly as desired. I just had to put in the "where" clause.
Thank you very much.