• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 664
  • Last Modified:

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 ()
0
enrique_aeo
Asked:
enrique_aeo
4 Solutions
 
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
 
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
 
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now