Generate numeric field automatically based on another field

Question

I have a table in SQL server with two columns, Name and Order. Name is a varchar column which contains student names, Order is an int column which contains the number in ascending order corresponding to the Name column in alphabetical order.

For example, the expected result should be like below

Names - Order

ABC - 2
AAA - 1
CCC - 3
XZZ - 5
XYZ - 4

Is there any option to generate the order column automatically whenever a new name gets inserted into the table?

Why do you need an order column? SQL Server will allow you to order alphabetically by the name column? And if you want a row number, us the row_number function. You don't need to store anything. — Dale K
– Dale K, Commented Nov 16, 2020 at 9:16

GMB · Accepted Answer · 2020-11-16 09:20:29Z

2

Order is an int column which contains the number in ascending order corresponding to the Name column in alphabetical order.

Just use row_number(). You should not be storing this derived information - instead, you can use a view:

create view myview as
select t.*, row_number() over(order by name) as rn
from mytable t

Note: order is a language keyword (as in order by ...), hence not a good choice for a column name; I used rn instead.

answered Nov 16, 2020 at 9:20

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Generate numeric field automatically based on another field

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related