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.