SQL Existing Column Conditional Update Query

I have this data

AnsID  QuesID  AnsOrder
-----------------------
1       5       NULL
2       5       NULL
3       5       NULL
4       5       NULL
5       5       NULL
6       3       NULL
7       3       NULL
8       3       NULL
9       3       NULL
10      3       NULL
11      4       NULL
12      4       NULL
13      4       NULL
14      4       NULL
15      4       NULL
16      7       NULL
17      9       NULL
18      9       NULL
19      9       NULL
20      9       NULL
21      8       NULL
22      8       NULL
23      8       NULL
24      8       NULL

Want to UPDATE it into this format

AnsID  QuesID  AnsOrder
-----------------------

1       5       1
2       5       2
3       5       3
4       5       4    
5       5       5
6       3       1
7       3       2
8       3       3
9       3       4
10      3       5
11      4       1
12      4       2
13      4       3
14      4       4
15      4       5
16      7       1
17      9       1
18      9       2
19      9       3
20      9       4
21      8       1
22      8       2
23      8       3
24      8       4

Basicaly I want to update AnsOrder column in ascending order according to QuesID column, like this for more readability.

AnsID  QuesID  AnsOrder
-----------------------

1       5       1
2       5       2
3       5       3
4       5       4    
5       5       5

6       3       1
7       3       2
8       3       3
9       3       4
10      3       5

11      4       1
12      4       2
13      4       3
14      4       4
15      4       5

16      7       1

17      9       1
18      9       2
19      9       3
20      9       4

21      8       1
22      8       2
23      8       3
24      8       4

Answers


You might generate row_numbers by quesID and assign them to AnsOrder like this:

; with ord as (
  select *,
         row_number() over (partition by quesID
                            order by AnsID) rn
    from table1
)
update ord 
   set ansorder = rn

I've ordered by AnsID for consistency. Check this @ Sql Fiddle.


Need Your Help

overflow hidden needed in container makes appear scrollbar

html css scrollbar overflow containers

I'm having a bit of trouble with overflow: hidden to give height to a container....

How to get "paster request" to use config host value instead of localhost?

python routes pylons paster

I'm trying to access my pylons application via cron job to send notifications to my users. The way I'm doing this is by running the application using something like: