Ritesh Shah is an author of many technical article on Microsoft Technology, especially on SQL-Server, .NET technology like C# and ASP.Net etc. He is having 10+ year of experience in IT industry and working as Consultant Project Leader and Database Administrator.
He is very active community member, you can find him at:
SQLHub.com
Experts-Exchange.com
Asp.Net
LinkedIN
Beyond-Relational
Twitter
SQL Server 2012 Performance Tuning book by Ritesh Shah
 | Soumya Ranjan Samant… on Image store in SQL-Server 2005… |
 | Jeff Moden on Table statistics in SQL Server… |
 | Twitter on Read XLS or MDB file with Open… |
 | Kelli Dudley on LOG function in SQL Server and… |
 | Jason on DDL Trigger in SQL-Server 2005… |
 | Amit Kumar Gupta on DDL Trigger in SQL-Server 2005… |
 | riteshshah on Image store in SQL-Server 2005… |
 | Soumya Ranjan Samant… on Image store in SQL-Server 2005… |
 | BYRON on Read XLS or MDB file with Open… |
| How to Remove Duplic… on Delete-Select-Duplicate-Record… |
Use of RANK() and Partition by clause in SQL-Server 2005
We will start by creating one table for demonstration and enter some records in it.
–Create one table
CREATE TABLE BlogCount
(
BloggerName VARCHAR(10),
Topic VARCHAR(15),
[Year] INT,
Total INT
)
Sorry for the inconvenience.
Extreme-Advice.com
Ritesh's Blog for SQL-SERVER & .NET
March 8, 2009 at 2:45 pm
[…] bookmarks tagged rank Use of RANK() and Partition by clause in SQL-Serve… saved by 3 others rokgod bookmarked on 03/08/09 | […]
March 2, 2010 at 7:38 am
if want output as follows
alka sql
.net
ritesh sql
.net
March 2, 2010 at 7:50 am
in that case RANK is not the thing you are looking for. you have to use COALESCE function group by name. You can refer my blog SQLHub.com for help at http://www.sqlhub.com/2008/04/coalesce-function-in-sql-server-for.html
March 11, 2010 at 10:28 pm
Thanks for the hints guys , it works beautifully :o)
July 26, 2011 at 11:40 am
How can we implement as normal query ?
July 26, 2011 at 12:14 pm
sorry I am not getting you clearly, can you please be little more specific about your query/concern?
July 26, 2011 at 12:33 pm
BTW Dhiraj, I am not looking this blog much, my active blog is SQLHub.com and I used be active there only. so you can continue same thread there too.
http://www.sqlhub.com/2009/03/use-of-rank-and-partition-by-clause-in.html
http://www.sqlhub.com/2009/10/windowing-partition-functions-rank.html
September 9, 2011 at 9:38 am
Query
I have a list of runners and their race times and ages. Write a
Query to display the runners sorted by finish time with a column showing
their finish position (ranking) within their age group.
The age groups are
0 – 15 years
16 – 29 years
30+ years
Name Time Age
——————–
Steve 12 33
Tim 34 28
Mark 22 37
Tom 21 30
Cliff 13 33
Vini 17 28
Matt 10 28
Ben 9 29
Brandon 15 14
Output should be like below
Name Time Age Ranking
===== ==== === =======
Ben 9 29 1
Matt 10 28 2
Steve 12 33 1
Cliff 13 33 2
Brandon 15 14 1
Vini 17 28 3
Tom 21 30 3
Mark 22 37 4
Tim 34 28 4
September 9, 2011 at 9:59 am
Hi Raju,
I am not using this blog much so I have given the answer for your query in my current live blog.
http://www.sqlhub.com/2009/03/use-of-rank-and-partition-by-clause-in.html
Have a look at it:
September 9, 2011 at 12:44 pm
Very clear explanation, thanks!
February 23, 2012 at 5:47 am
This rocks!! Many thanks for sharing!!
April 13, 2012 at 10:38 am
This helped me. Thanks a lot 🙂
April 24, 2012 at 1:56 pm
Elegant Themes, woothemes, themforest, premium wordpress template, free wordpress…
[…]Use of RANK() and Partition by clause in SQL-Server 2005 « Ritesh’s Blog for SQL-SERVER & .NET[…]…