Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Ranking

Status
Not open for further replies.

PBS2

Programmer
May 17, 2004
43
GB
I have a table which needs to look like this:-

Branch# Sales Rank
1 325 3
2 450 2
3 301 4
4 206 5
5 598 1

My stored procedure updates the Branch# and Sales figure correctly. What SQL code can I write to update the Rank column (NB. this is ranking by Sales).


 
Try this:

Code:
UPDATE table
SET rank = (
    SELECT COUNT(*)
    FROM table
    WHERE sales >= t1.sales
  )
FROM table t1

--James
 
Hi,

If it's few records in the table, I would create a stored procedure that updates the current Branch, then after that I would create a temp table with identity id (the rank) and insert the rows from your originally table ordered by Sales.

After that update the rank from the temp table.

I'll hope this can help you.

Regards
/Mattias

ps. another solution is to calculate the rank everytime you fetch the data, depending on your solution.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top