|
flamblaster
Constraint Violating Yak Guru
384 Posts |
Posted - 2012-07-08 : 03:08:06
|
| Yes, you can use the Row_Number() function. You need to determine how it will be ordered though in order to set up a sequence.example:declare @Customers table (CustomerId int, LastName varchar(20), FirstName varchar(20), Gender char(1))insert into @Customers (CustomerId, LastName, FirstName, Gender)values(1, 'Jones', 'Martha', 'F'),(2, 'Tyler', 'Rose', 'F'),(3, 'Noble', 'Donna', 'F'),(4, 'Jenkins', 'Sam', 'M'),(5, 'Dennison', 'Kyle', 'M'),(6, 'Atkins', 'Jim', 'M')select CustomerId, Lastname, Firstname, Gender, ROW_NUMBER() OVER (Partition By Gender Order by Lastname, Firstname) as Sequencefrom @Customers(6 row(s) affected)CustomerId LastName FirstName Gender Sequence----------- -------------------- -------------------- ------ --------------------1 Jones Martha F 13 Noble Donna F 22 Tyler Rose F 36 Atkins Jim M 15 Dennison Kyle M 24 Jenkins Sam M 3(6 row(s) affected)In the sample problem, you'll see that you the query is setting up a sequence based on the gender and sorted by lastname then firstname. As long as you know the criteria for which you want to create a sequence, you should be good to go. |
 |
|
Numbering rows in a select