Ranking

[PBS2]

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).

 

[JamesLean]

Try this:

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

--James

 

[MattiasJ]

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.