Number/grade counts within a table 1

[davida37]

hi,

I have a table:

TaskID URLID Order Total
1 731951 NULL 100
1 731952 NULL 10
1 731953 NULL 9
2 1 NULL 100
2 2 NULL 10
2 3 NULL 9

I want to update the Order column so that it looks at the total counts and gives a grading by TaskID. so the highest total per taskId would be 1, the 2nd highest total per Task Id would be 2 and so on. There will be more than 3 per TaskID however. So it would look like the below.

TaskID URLID Order Total
1 731951 1 100
1 731952 2 10
1 731953 3 9
2 1 1 100
2 2 2 10
2 3 3 9

do I need a cursor for this?

Thanks

 

[bborissov]

[COLOR=green]---- Preparing example data
[/color][COLOR=green]---- you don't need this
[/color][COLOR=blue]DECLARE[/color]  @Temp [COLOR=blue]TABLE[/color] (TaskID [COLOR=blue]Int[/color], URLID [COLOR=blue]int[/color], [[COLOR=blue]Order[/color]] [COLOR=blue]int[/color] NULL, Total [COLOR=blue]int[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color](1, 731951, NULL, 100)        
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color](1, 731952, NULL, 10)       
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color](1, 731953, NULL, 9)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color](2, 1, NULL, 100)    
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color](2, 2, NULL, 10)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color](2, 3, NULL, 9)
[COLOR=green]--- End of preparing
[/color]

[COLOR=green]-- We need this table variable 
[/color][COLOR=blue]DECLARE[/color]  @Temp1 [COLOR=blue]TABLE[/color] (TaskID [COLOR=blue]int[/color] , Total [COLOR=blue]int[/color], Id [COLOR=blue]int[/color] [COLOR=blue]IDENTITY[/color](1,1))

[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp1
[COLOR=blue]SELECT[/color] TaskID, Total
       [COLOR=blue]FROM[/color] @Temp
[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] TaskID, Total



[COLOR=green]--- So let's update the table with example data
[/color][COLOR=blue]UPDATE[/color] [COLOR=blue]Temp[/color] [COLOR=blue]SET[/color] [[COLOR=blue]Order[/color]] = (Tmp1.MaxId - Tmp1.Id + 1)
[COLOR=blue]FROM[/color] @Temp [COLOR=blue]Temp[/color]
[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] Tmp.TaskId, Total, Tmp.Id, Tmp2.MaxId
                   [COLOR=blue]FROM[/color] @Temp1 Tmp
                   [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] TaskId, [COLOR=#FF00FF]MAX[/color](Id) [COLOR=blue]AS[/color] MaxId
                                      [COLOR=blue]FROM[/color] @Temp1 [COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] TaskId) Tmp2
                   [COLOR=blue]ON[/color] Tmp.TaskId = Tmp2.TaskId) Tmp1
[COLOR=blue]ON[/color] [COLOR=blue]Temp[/color].TaskId = Tmp1.TaskId AND
   [COLOR=blue]Temp[/color].Total  = Tmp1.Total

[COLOR=blue]SELECT[/color] * [COLOR=blue]from[/color] @Temp

[COLOR=green]--- If that query satify your needs just remove example data
[/color][COLOR=green]--- and change all @Temp to your actual table name
[/color][COLOR=green]--- MAKE SURE YOU HAVE A VERY GOOD BACKUP FIRST[/color]

In SQL Server 2005 you have other options, but I don't know what version you use, so I used a syntax that should works in 200 too.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[davida37]

Hi Borislav,

thanks very much. this is very helpful!

I am actually using SQL server 2005. ...please dont write it full blown - but as a pointer what would you do different with 2005 syntax?

Thanks again..

 

[bborissov]

There are various RANKing function there. You could check them in BOL.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[davida37]

Thanks Borislav