June 27, 2007 at 4:06 pm
I would like to be able to run an update query
that updates a rank field in a table with a rank order.
I can use the rank over function in a sproc or query to show the rank result.
However, sometimes I need to extract subsets of the main table, along with ranking info.
If I try to use parameters with the sproc it does not return the correct rank.
If I try to write a function to extract the rank from a specific record, then this does not work either, and if I try to create an update query it tells me that the rank function cannot be used.
Neither can I use it as part of a view.
Since I've not used this before today, I am wondering if there is a simple solution.
Ideally I would like a function that will extract the rank of a particular record from the main table, or a means to write the rank to a field.
I have a work around that uses a table function and then runs the rank extract from this, but it is slow.
Any pointers gratefully accepted.
As a general example suppose I have a table with columns
ID, GROUP, VALUE
1, one, 20
2, one, 24
3, one, 11
4, two, 15 etc
I need to get a rank order over the group based on value.
Easy in a TSQL routine.
However what I would ideally like is a function that
if passed the ID will return the Rank order within it's group of that ID.
So if I passed the function an ID of 2
It would return a rank of 1 because that is the highest in Group one.
Using Rank in the sproc does this but I need the function that just returns the rank for the one ID.
Probably easy, but I'm stuck on it.
All help gratefully accepted.
Andrew
June 28, 2007 at 12:46 am
something similar to this
declare
@sample table (id tinyint, [group] char(3), value tinyint, [rank] tinyint)
insert
@sample
select 1, 'one', 20 union all
select 2, 'one', 24 union all
select 3, 'one', 11 union all
select 4, 'two', 15
select
* from @sample
update
t
set t.[rank] = t.x
from (
select dense_rank() over (partition by [group] order by value desc) as x
from @sample
) as t
select
* from @sample
N 56°04'39.16"
E 12°55'05.25"
June 30, 2007 at 3:08 pm
I'll experiment with this and see if embedding the table variable in the function makes it perform any quicker than having it as a separate function.
Thanks for input.
Andrew
July 2, 2007 at 7:18 pm
I used this method, although I did not need to use table function because it worked fine on table direct.
I did need to reset rank to null where value was null
because the routine as shown ranked nulls as 1 if all values null.
Actual routine I used is as below
If you can think of a method to improve it then even better
but it runs very quickly with a table of over 12000 entries.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE procRankUpdate
AS
BEGIN
SET NOCOUNT ON;
UPDATE d
SET d.crd_Rank = d.cRank
FROM (SELECT Rank() OVER (PARTITION BY crd_CID
ORDER BY crd_Assessment DESC) AS cRank,
crd_Assessment,
crd_Rank
FROM ASSESSMENTS
) AS d
-- Then Reset any ranks to Null if assessment is null
update ASSESSMENTS
set crd_rank=null
where crd_Assessment Is Null
END
Thanks
AW
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply