Need help in query - 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 2008 Forums

Transact-SQL (2008)

Need help in query

Author

Topic

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2013-01-10 : 02:23:28

Hi
I have a table named QuizHistory with these fields : quizId,userId,mark

I want to write a query to get a user's rank for a particular quizId

Thanks a lot

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-01-10 : 02:56:39

Do you have an example (example data and wanted result)?

Too old to Rock'n'Roll too young to die.

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2013-01-10 : 03:12:58

assume here is the records in the table
quizId, userId, mark
1, 1, 85
1, 2, 72
1, 3, 94
1, 4, 65

the rank for userId=1 would be 2 (means he is the second person in this quiz)
the rank for userId=2 would be 3 (means he is the third person in this quiz)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-10 : 03:22:24

[code]
SELECT userId,Rnk
FROM
(
SELECT DENSE_RANK() OVER (PARTITION BY quizId ORDER BY mark) AS rnk,*
FROM table
)t
WHERE quizId=@Yourpassedquizid
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2013-01-10 : 04:29:48

thanks a lot.
it work's perfectly

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-10 : 04:35:26

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources