0

I am looking for assistance to add a column to my table which sequences (1,2,3...) each row based on date and grouped by userID as shown in the mockup below. Not sure if I have to join the table on itself and count, or whether there is a more efficient function. Im using SQL Server 2008 R2. 

| Username  | Date      | 
--------------------------
| Username1 | 01/1/2014 | 
| Username1 | 02/1/2014 | 
| Username1 | 03/1/2014 |
| Username2 | 01/4/2014 | 
| Username2 | 01/6/2014 | 
| Username3 | 02/3/2014 |
| Username3 | 05/8/2014 |
| Username4 | 01/9/2014 |
| Username1 | 08/1/2014 | 
| Username5 | 07/1/2014 | 

| Username  | Date      |Seq |
------------------------------
| Username1 | 01/1/2014 | 1  |
| Username1 | 02/1/2014 | 2  |
| Username1 | 03/1/2014 | 3  |
| Username2 | 01/4/2014 | 1  | 
| Username2 | 01/6/2014 | 2  |
| Username3 | 02/3/2014 | 1  | 
| Username3 | 05/8/2014 | 2  |
| Username4 | 01/9/2014 | 1  | 
| Username1 | 08/1/2014 | 4  |
| Username5 | 07/1/2014 | 1  |
Alex K.
  • 171,639
  • 30
  • 264
  • 288
user2036380
  • 29
  • 2
  • 1
    Why create a column for something you can generate at query-time? What if a new row gets inserted into the middle of a sequence? You'll have to re-calculate everything in that sequence. – Tab Alleman Aug 04 '14 at 19:09
  • It would probably not be appropriate to add the sequence as a column to the table except for special situations. This can be done easily within a sql query. If you provide us with more information on what youve done to get the table shown (SQL) it will help us come up with better suggestions. For information on using ROW_NUMBER RANK and DENSE_RANK check here http://stackoverflow.com/questions/5253311/how-to-add-sequence-number-for-groups-in-a-sql-query-without-temp-tables – Jwit Aug 04 '14 at 19:13

2 Answers2

1

A row number partitioning by username:

select 
    username, 
    date,
    row_number() over(partition by username order by username, date) as seq
from T
Alex K.
  • 171,639
  • 30
  • 264
  • 288
1

This is perfect for the row_number() function:

select username, date, row_number() over (partition by username order by date) as seq
from mytable;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786