Top queries with rank - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Top queries with rank

Author

Topic

Keshaba
Yak Posting Veteran

52 Posts

Posted - 2008-12-09 : 06:31:52

I am using Sql server 2005 express .I want to write a query where I can get Top 10 marks as well as I want to rank them 1, 2, 3.The ranking should be best on the order of marks obtained.I can write the Top 10 query as follows but I don't know how to rank them .I tried like this

select TOP 10 Marks from mark_up order by Marks desc

But I want the answer to come like this

Rank Marks
1 100

2 60

2 60

3 45

4 35

5 30

Keshab

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-09 : 06:55:41

[code]DECLARE @Top TABLE
(
Rank INT,
Marks INT
)

INSERT @Top
SELECT 1,
Mark
FROM Table1

UPDATE t
SET t.Rank = (SELECT COUNT(DISTINCT Marks) FROM @Top AS x WHERE x.Marks >= t.Marks)
FROM @Top AS t

SELECT *
FROM @Top
ORDER BY Rank[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources