Returning Row number in Query

I wanted to return the row number with the data set in my query. I achieve that without much effort but after that I had to modify that to return the line number according to a group as,

StudentName CourseName      Row Number
---------- ------------- ---------
Nalaka      Computer Science 1
Nalaka      Economics        2
Nalaka      Marketing        3
Dineesh     Economics        1
Dineesh     Marketing        2
Indika      Computer Science 1

After some time I found the solution. It's very interesting and I think It's a breakthrough to SQL beginners where they can expand their SQL knowledge. Here it is.

create table #Student(StudentID int,StudentName varchar(20))
create table #Course(CourseID int,CourseName varchar(20))
create table #CourseDetails(StudentID int,CourseID int)

insert into #Student values(1,'Nalaka')
insert into #Student values(2,'Dineesh')
insert into #Student values(3,'Indika')

insert into #Course values(1,'Computer Science')
insert into #Course values(2,'Economics')
insert into #Course values(3,'Marketing')

insert into #CourseDetails values(1,1)
insert into #CourseDetails values(1,2)
insert into #CourseDetails values(1,3)
insert into #CourseDetails values(2,2)
insert into #CourseDetails values(2,3)
insert into #CourseDetails values(3,1)


select S.StudentName,C.CourseName,
(select count(*) from #CourseDetails CD1 where CD1.StudentID = CD.StudentID and CD1.CourseID <= CD.CourseID) as 'Row Number' from #Student S, #Course C, #CourseDetails CD where S.StudentID=CD.StudentID AND C.CourseID=CD.CourseID order by CD.StudentID,CD.CourseID

drop table #Student
drop table #Course
drop table #CourseDetails

Paste this in your SQL Query Analyzer and run. You'll get the result with the row number as I expected earlier. It's bit tricky. examine "(select count(*) from #CourseDetails CD1 where CD1.StudentID = CD.StudentID and CD1.CourseID <= CD.CourseID) as 'Row Number'" part of the query with the data in #CourseDetails table.