Saturday, 19 April 2008

Sql server 2005 : OVER clause

I found it very useful. Let me give you a quick overview of the OVER clause. Not very detailed but a will prove a nice kick start for you guys.

Lets take a table for our example:

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

Lets now write some inserts for the table:

insert into marks values(1,'sanjeev','Math',87)
insert into marks values(1,'sanjeev','Geography',76)
insert into marks values(1,'sanjeev','History',98)
insert into marks values(1,'sanjeev','Science',85)
insert into marks values(2,'gourav','Crafts',89)
insert into marks values(2,'gourav','Science',88)
insert into marks values(2,'gourav','History',76)
insert into marks values(3,'Munish','English',87)
insert into marks values(3,'Munish','Science',76)
insert into marks values(3,'Munish','Geography',83)

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

sanjeev History 98
sanjeev Math 87
gourav Crafts 89
gourav Science 88
Munish English 87
Munish Geography 83

In SQL 2000 we would have used a correlated subquery, somewhat like this:

select studentname, subject, score
from marks o
where score in (select top 2 score from marks 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 marks
) 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

i found it really cool. My thanks to gourav for bringing this to my attention.

kick it on

No comments:

Post a Comment