sql server 2008 ,RANK() OVER (ORDER BY a.PostalCode) AS 'Rank'

Hi experts,
Can you give me an example where you may use the function rank ()
enrique_aeoAsked:
Who is Participating?
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Here's my RANK and PARTITION T-SQL study script.  Knock yourself out.

/*
RANK and PARTITION training
http://weblogs.sqlteam.com/jeffs/archive/2007/03/28/60146.aspx

ROW_NUMBER
http://msdn.microsoft.com/en-us/library/ms186734.aspx

01-25-12   jhorn   Original
02-15-12   jhorn   Added two-column RANK, one overall, one by team
*/

IF OBJECT_ID('Batting') IS NOT NULL
	DROP TABLE Batting
GO

create table Batting (Player varchar(10), Year int, Team varchar(10), HomeRuns int, primary key(Player,Year))
 
insert into Batting
 select 'Arnie',2001,'Red Sox',13 union all
 select 'Arnie',2002,'Red Sox',23 union all
 select 'Arnie',2003,'Red Sox',19 union all
 select 'Arnie',2004,'Red Sox',14 union all
 select 'Arnie',2005,'Red Sox',11 union all
 select 'Bob',2001,'Yankees',42 union all
 select 'Bob',2002,'Yankees',39 union all
 select 'Bob',2003,'Yankees',41 union all
 select 'Bob',2004,'Yankees',29 union all
 select 'Casey',2002,'Yankees',2 union all
 select 'Casey',2003,'Yankees',3 union all
 select 'Casey',2004,'Red Sox',6 union all
 select 'Casey',2005,'Red Sox',9 union all
 select 'Doug',2003,'Yankees',13 union all
 select 'Doug',2004,'Red Sox',26 union all
 select 'Doug',2005,'Red Sox',19 union all
 select 'Ernie',2003,'Tigers',33 union all
 select 'Ernie',2004,'Tigers',26 union all
 select 'Ernie',2005,'Tigers',19 union all
 select 'Fred',2003,'Indians',3 union all
 select 'Fred',2004,'Indians',4 union all
 select 'Fred',2005,'Indians',7 union all
 select 'Gary',2003,'Orioles',33 union all
 select 'Gary',2004,'Orioles',26 union all
 select 'Gary',2005,'Orioles',19 union all
 select 'Hank',2003,'Brewers',20 union all
 select 'Hank',2004,'Brewers',20 union all
 select 'Hank',2005,'Brewers',20
 
-- TWO COLUMNS WITH RANK, ONE OVERALL, ONE FOR TEAM
SELECT Player, Team, Year, HomeRuns, 
	Rank() over (Partition BY Team order by HomeRuns DESC) as rank_team,
	RANK() OVER (ORDER BY HomeRuns DESC) as rank_overall
FROM 
BATTING


-- TOP 5 Home Runs by player by team
With HomeRunsSeason as (
	SELECT 
		RANK() OVER (ORDER BY SUM(HomeRuns) desc) as No,
		Player, 
		Team, 
		SUM(HomeRuns) as HR
	FROM Batting
	GROUP BY Player, Team )
SELECT *
FROM HomeRunsSeason
WHERE No <= 5


-- HomeRuns grouped by player and team
SELECT DISTINCT Team, 
	SUM(HomeRuns) OVER (PARTITION BY Player, Team ) as home_run_total
FROM Batting

SELECT Player, Team, SUM(HomeRuns) as home_run_total
FROM Batting
GROUP BY Player, Team
ORDER BY Player, Team
GO



-- RANK
select Player, Year, HomeRuns, Rank() over (Partition BY Player order by HomeRuns DESC) as Rank
 from 
Batting

select Player, Year, Team, HomeRuns,Rank() over (Partition BY Player order by Year, HomeRuns DESC) as Rank
 from 
Batting
 
SELECT Player, Year, Team, HomeRuns, RANK() over (Partition by team order by HomeRuns desc) as rank
FROM Batting

SELECT a.Player, a.[Year], a.Team, a.HomeRuns, a.[rank]
FROM (
	select Player, [Year], Team, HomeRuns, Rank() over (Partition BY Player order by HomeRuns DESC, Year DESC) as [rank]
	from Batting ) a
WHERE a.[rank]=1



-- Using OVER with ROW_NUMBER
USE AdventureWorks2008R2;
GO
SELECT p.FirstName, p.LastName
    ,ROW_NUMBER() OVER(ORDER BY SalesYTD desc) AS 'Row Number'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s 
    INNER JOIN Person.Person p ON s.BusinessEntityID = p.BusinessEntityID
    INNER JOIN Person.Address a ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0
GO

-- Using OVER with aggregate
USE AdventureWorks2008R2;
GO
SELECT SalesOrderID, ProductID, OrderQty
    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total'
    ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Avg'
    ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Count'
    ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Min'
    ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Max'
FROM Sales.SalesOrderDetail 
WHERE SalesOrderID IN(43659,43664);
GO

-- The following example shows using the OVER clause with an aggregate function in a calculated value.
USE AdventureWorks2008R2;
GO
SELECT SalesOrderID, ProductID, OrderQty
    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total'
    ,CAST(1. * OrderQty / SUM(OrderQty) OVER(PARTITION BY SalesOrderID) 
        *100 AS DECIMAL(5,2))AS 'Percent by ProductID'
FROM Sales.SalesOrderDetail 
WHERE SalesOrderID IN(43659,43664);
GO

Open in new window

0
 
lwadwellCommented:
When you need to understand the 'finishing position' of something, e.g. students on an exams
SELECT student, rank() over(order by score) as rank
FROM exam_results

rank() is used when you may have 'tied' results ... eg
with exam_results as (
select 'freddie' as student, 88 as score union all
select 'jane' as student, 87 as score union all
select 'may' as student, 87 as score union all
select 'bill' as student, 42 as score 
)
select student, rank() over(order by score desc) as rank
FROM exam_results

Open in new window

0
 
CrashmanCommented:
rank() is a Classification function, shows the number of occurrences based in the partion using the before example, you can see how show how many times freddie shows, and the other shows how many times shows freddie using section as conditional column

;with exam_resultsA as (
select 'freddie' as student, 88 as score , 'A' as section union ALL
select 'freddie' as student, 89 as score ,'A' as section union all
select 'jane' as student, 87 as score, 'B' AS Section union all
select 'may' as student, 87 as score, 'B' AS section union all
select 'bill' as student, 42 as score , 'B' AS section 
)
select student, rank() over( order by score desc) as rank
FROM exam_resultsA

;with exam_resultsA as (
select 'freddie' as student, 88 as score , 'A' as section union ALL
select 'freddie' as student, 89 as score ,'B' as section union all
select 'jane' as student, 87 as score, 'B' AS Section union all
select 'may' as student, 87 as score, 'B' AS section union all
select 'bill' as student, 42 as score , 'B' AS section 
)
select student, rank() over(partition by section order by score desc) as rank
FROM exam_resultsA


;with exam_resultsA as (
select 'freddie' as student, 88 as score , 'A' as section union ALL
select 'freddie' as student, 89 as score ,'A' as section union all
select 'jane' as student, 87 as score, 'B' AS Section union all
select 'may' as student, 87 as score, 'B' AS section union all
select 'bill' as student, 42 as score , 'B' AS section 
)
select student, rank() over(partition by section order by score desc) as rank
FROM exam_resultsA
                                                                                        
                                                                                        

Open in new window

0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
enrique_aeoAuthor Commented:
ok
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the split.  Good luck with your learning.  -Jim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.