Cool new OVER Clause (Transact-SQL) in SQL Server 2005

Cool new OVER Clause (Transact-SQL) in SQL Server 2005 to circumvent the not so efficient correlated subquery.

Imagine a table:

create table grades(
 StudentID int not null
,StudentName varchar(10) not null
,Subject varchar(10) not null
,Score  int not null)

With some values:

insert into grades values(1,'John','Math',87)
insert into grades values(1,'John','Geography',76)
insert into grades values(1,'John','History',98)
insert into grades values(1,'John','Science',85)
insert into grades values(2,'Sally','Crafts',89)
insert into grades values(2,'Sally','Science',88)
insert into grades values(2,'Sally','History',76)
insert into grades values(3,'Molly','English',87)
insert into grades values(3,'Molly','Science',76)
insert into grades values(3,'Molly','Geography',83)

You want to return each student with their highest two scoring subjects e.g.

John    History    98
John    Math       87
Sally   Crafts     89
Sally   Science    88
Molly   English    87
Molly   Geography  83

In SQL 2000 you may have used a correlated subquery thus:

select studentname, subject, score
from grades o
where score in (select top 2 score from grades i where i.studentname = o.studentname order by score desc)
order by studentname,score desc

However much more efficient and simpler in SQL 2005 is the new OVER clause:

select studentname, subject, score
   select studentname, subject, score,
   row_number() over (partition by studentid order by score desc) as rownum
   from grades 
) dt
where rownum<=2

The OVER clause: Determines the partitioning and ordering of the rowset before the associated window function is applied.  Applies to: Ranking Window functions

Very cool me thinks.  My thanks to David Browne and Alfredo Ramirez for bringing this to my attention.

Comments (1)

  1. Meghana says:

    It indeed is a cool feature. I think it also makes a lot of difference performance wise because of the partition

Skip to main content