Partager via

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

  • Article

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
from
(
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

  • Anonymous
    June 28, 2005
    It indeed is a cool feature. I think it also makes a lot of difference performance wise because of the partition