Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
dzabor
Posting Yak Master
138 Posts |
Posted - 2013-04-29 : 18:31:14
|
I am trying to update a table based on a count of company records:select Top 100 co_sort, count (*) as countfrom Institute IIinner join Name non n.ID = ii.idgroup by co_sortorder by count descAbove query returns:ABC CO 9699Ernst CO 7532Coopers 7463KPMGYO 7022IBMB 1865I would like to update the CO_Record_Count in my existing table 'Company' (below):ID, CO_Sort, CO_Record_Count, CO_Rankupdate cset CO_Record_Count = countfrom company cI can use CO_Sort to join. It will udpate each ID with the company count. I am also trying to rank the company, but that is not as urgent.Thanks for any help you can provide. |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-29 : 18:35:45
|
Combining the two queries you posted as shown below. If you remove the TOP 100, be sure to also remove the ORDER BY clauseWITH cte AS ( SELECT TOP 100 co_sort , COUNT(*) AS count FROM ISACA_Institute II INNER JOIN Name n ON n.ID = ii.id GROUP BY co_sort ORDER BY count DESC )UPDATE cSET CO_Record_Count = countFROM company c INNER JOIN cte t ON t.CO_Sort = c.CO_Sort; |
|
|
dzabor
Posting Yak Master
138 Posts |
Posted - 2013-04-29 : 18:51:57
|
Thanks - I will try. I was just playing with a temp table and got it to work, but will see what this does - seems better. Do you know anything about ranking? I need to rank the top 100 and add to the records. |
|
|
dzabor
Posting Yak Master
138 Posts |
Posted - 2013-04-29 : 19:26:43
|
I actually got this far: select top 100 co_record_count, RANK ( ) OVER ( order by co_record_count desc) as rank from CO_Top100Now I need to find a way to update my table as below:update cset CO_Rank = Rankfrom CO_Top100 |
|
|
|
|
|