Alternative for row_number() in sql server 2000

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Alternative for row_number() in sql server 2000

Author

Topic

pinkysam
Starting Member

2 Posts

Posted - 2014-09-23 : 05:17:55

Option for row_number and partition by in sql server 2000?

Here is my table Student_Grade

id StudentName subcode,Grade StdId
1 pinky 1 A 1
2 Archana 2 A 1
3 leena 1 A 1
4 leena 2 A 1
5 leena 3 B 1
6 Rupali 1 A 2
7 Megha 1 A 2
8 smita 1 A 1

Grade are from A to D.I want to show record only once when grade is same for same stid,does not matter what is subcode and studentname.For perticular subcode,appropriate studentname come when stid and grade specidy.If grade is different for same stid,show both record. Here I want to show

I applied row_numer() working properly in sql 2005 not working 2000.How to do in 2000 Query is :

SELECT StdId,SubCode,Studentname,Grade,subjectname
FROM (SELECT StdId,SubCode,Studentname ,Grade,Subjects.subjectname as subjectname,
Row_number() OVER(PARTITION BY cast(StdId as varchar) +' - '+ Grade ORDER BY SubCode desc,Studentname desc) rn
FROM Student_Grade
inner join Subjects
on Subjects.id = Student_Grade.SubCode
) t
WHERE rn = 1
Result :

stdid subcode Studentname Grade SubjectName
1 2 leena A Marathi
1 3 leena B Hindi
2 1 Rupali A English

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-23 : 14:19:08

Save your intermediate results in a temporary table, adding an identity column:


select rn = identity(int, 1, 1), ... --other columns
into #temp
from (select top 100 percent ...
      from ...
      order by ...
     ) q

You'll get your results with auto-generated ids, counting from 1, in your new temp table. Should be the same effect as row_number() albeit with extra I/O.

Note 1: you can only use select identity... when using INTO.

Note 2: I do not have sql server 2000 to test this on.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources