June 27, 2006 at 2:04 pm
Hi i have table like below
SNONAMEMARKSRANK
----------------------------
1A56
2B35
3C77
4D86
5E35
6F77
7G63
8H42
9I51
Now i have to give ranks marks wise like this
SNONAMEMARKSRANK
----------------------------
1A565
2B358
3C772
4D861
5E358
6F772
7G634
8H427
9I516
in above table there is no 3rd and 9th Rank,because C,F shred the 2nd rank
and B and E shred the 8th rank
to generate this type of result some body help me
June 27, 2006 at 2:17 pm
Something like this?
-- prepare test data
DECLARE @test-2 TABLE (SNO TINYINT, NAME VARCHAR, MARKS TINYINT)
INSERT @test-2
SELECT 1, 'A', 56 UNION ALL
SELECT 2, 'B', 35 UNION ALL
SELECT 3, 'C', 77 UNION ALL
SELECT 4, 'D', 86 UNION ALL
SELECT 5, 'E', 35 UNION ALL
SELECT 6, 'F', 77 UNION ALL
SELECT 7, 'G', 63 UNION ALL
SELECT 8, 'H', 42 UNION ALL
SELECT 9, 'I', 51
-- do the work
SELECT a.*,
(SELECT 1 + COUNT(*) FROM @test-2 b WHERE b.MARKS > a.MARKS) RANK
FROM @test a
N 56°04'39.16"
E 12°55'05.25"
June 27, 2006 at 2:50 pm
SreenivasaRao:
Update
MyTable
Set
RANK =
(
Select
(Count(*) + 1)
From
MyTable t2
Where
t2.MARKS > t1.MARKS
)
From
MyTable t1
June 27, 2006 at 2:53 pm
In SQL 2005, this is done with RANK()/OVER:
SELECT
Sno, Name, marks, RANK() OVER ( Order By Marks desc) as Rank
FROM YourTablename
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply