Hi... In my database i have tables like tblsubject(sidsname)tblexam(eidename)tblexaminf0(exideidmaxmarksminmarks)tblprogress(pidexidseuredmarks)tblstudadmisform(studentidclassnamefname) i want to display ranks of the sutdents i wirte query like ALTER PROCEDURE [dbo].[Sp_getProgress] @studentid varchar(50) @examTypeId int @ScId int @BrId int as select regd_no SubjectNameCONVERT(intMaxMarks) as MaxMarksCONVERT(intSecuredMarks) as SecuredMarksei.examIdExamTypeminmarks--sa.Class_admissionei.examinfoId case when CONVERT(intSecuredMarks)<CONVERT(intminmarks) then 'Fail' else 'Pass' end [Result] from tblexaminfo ei left outer join tblSubject on tblSubject.subjectid=ei.subid left Outer Join tblprogress p on p.examinfoid=ei.examinfoId left outer join tblstudadmisform sa on sa.regd_no=p.studentid left outer join tblExam e on e.examId=ei.examId where e.examId=@examTypeId and p.ScId=@ScId and p.BrId=@BrId and p.studentid=@studentid group by ei.examIdSubjectNameMaxMarksSecuredMarksExamTyperegd_nominmarks--Class_admissionei.examinfoId select sa.regd_nosa.stud_namec.classnamesum(convert(intSecuredMarks)) as totRANK() OVER (ORDER BY sum(convert(intSecuredMarks)) DESC) AS Rank into #temp from tblexaminfo ei left outer join tblSubject on tblSubject.subjectid=ei.subid left Outer Join tblprogress p on p.examinfoid=ei.examinfoId left outer join tblExam e on e.examId=ei.examId left outer join tblstudadmisform sa on sa.regd_no=p.studentid left Outer join tblClass c on c.ClassId=sa.class_admission where p.ScId=@ScId and p.BrId=@BrId and ei.examid=@examTypeId and ei.classid=sa.class_admission group by p.studentid sa.stud_namec.classnamesa.regd_no select * from #temp where regd_no=@studentid delete #temp but my problem is i want display ranks of the student but i dont want dispaly who were failed in any subject inplace rank i want to give fail. if any one have solution pls give replay Thanks Satya

Answer

Problem With Rank()

satya kishore

15y

2.6k

Hi... In my database i have tables like tblsubject(sid,sname),tblexam(eid,ename),tblexaminf0(exid,eid,maxmarks,minmarks),tblprogress(pid,exid,seuredmarks)tblstudadmisform(studentid,class,name,fname) i want to display ranks of the sutdents i wirte query like ALTER PROCEDURE [dbo].[Sp_getProgress] @studentid varchar(50), @examTypeId int , @ScId int, @BrId int as select regd_no, SubjectName,CONVERT(int,MaxMarks) as MaxMarks,CONVERT(int,SecuredMarks) as SecuredMarks,ei.examId,ExamType,minmarks,--sa.Class_admission,ei.examinfoId, case when CONVERT(int,SecuredMarks)