Ordering Rows using a column value
-
Hi all
Im trying to use a rankorder column to enable users to set the display order of the rows in a small table (<100). Currently when they insert a new row it takes the max(rankorder)+1 value as its own rankorder so they always end up at the end.
I need a 'clever' way or reordering and resequenceing the rankorder column to clear out all the gaps that will occur when rows are deleted. I am imaging that this will be done prior to adding a row or after deleting a row or both.
Thanks
Rolf
-
declare @rank int
set @rank = 0
update <table>
set @rank = rankorder = @rank + 1
For each row in the table processed, the value of @rank is incremented by 1.
J
-
Hi,
I am not sure if this helps, because it may not be what you are trying to achieve, but I was wondering if, rather than store the value in the table, you could write a function which would use the parameters passed to it to calculate a ranking value 'on-the-fly', and then order by this value in the result set?
e.g. a simple example:
CREATE FUNCTION dbo.fn_ModRank (@intModParam INT, @intRowValue INT )
RETURNS INT AS
BEGIN
RETURN @intRowValue % @intModParam
END
GO
CREATE TABLE RankTest (RawValue INT)
GO
INSERT INTO RankTest (RawValue)
SELECT 30 UNION
SELECT 9 UNION
SELECT 4 UNION
SELECT 11 UNION
SELECT 64 UNION
SELECT 66 UNION
SELECT 82 UNION
SELECT 3
GO
SELECT dbo.fn_ModRank(3,RawValue) AS 'Rank' , RawValue
FROM RankTest
ORDER BY 1,2
GO
SELECT dbo.fn_ModRank(5,RawValue) AS 'Rank', RawValue
FROM RankTest
ORDER BY 1,2
David
If it ain't broke, don't fix it...
-
update
set @rank = rankorder = @rank + 1
that will certainly reset all the columns to a sequential run but it will also reset their order! as it will just rank them in the order that msSQL reads them and I cant use an orderby clause in the update statement!
-
It will work if there is a clustered index on RankOrder column.
Or
UPDATE t1
SET t1.RankOrder = (SELECT COUNT(*) FROM Table1 AS t2 WHERE t2.RankOrder >= t1.RankOrder) -- If not working, try with <= instead.
FROM Table1 AS t1
N 56°04'39.16"
E 12°55'05.25" -
Peter,
on my browser your post appears inside the one posted by kangarolf
How did you do that, some clever fiddletrickery with the frames?
Ooer.. this one does as well, wierd
David
If it ain't broke, don't fix it...
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply