Set Group wise rank using Rank function in SQL Server

Last Reply 2 months ago By pandeyism

Posted 2 months ago

how to get the correct and accurate rank?

I have a data like this tblSetMarks

StuID Subject Obtain Max
R-1 Islamiate 34 50
R-2 Islamiate 43 50
R-3 Islamiate 34 50
R-4 Islamiate 5 50
R-5 Islamiate 32 50
R-1 Urdu 43 50
R-2 Urdu 34 50
R-3 Urdu 26 50
R-4 Urdu 28 50
R-5 Urdu 32 50

now i want to get output like this in sql server

Required Output
StuID SubjectName Max Obtain Rank
R-1 Islamiate 50 34 2
R-1 Urdu 50 43 1
SubjectName Max Obtain Rank
R-2 Islamiate 50 43 1
R-2 Urdu 50 34 2
SubjectName Max Obtain Rank
R-3 Islamiate 50 34 2
R-3 Urdu 50 26 5
SubjectName Max Obtain Rank
R-4 Islamiate 50 5 4
R-4 Urdu 50 28 4
SubjectName Max Obtain Rank
R-5 Islamiate 50 32 3
R-5 Urdu 50 32 3

where as my query is showing me wrong rank

            SELECT SPic,SessionName,ExamType,fa.AdmissionNo,ReferenceNo,SName,FName,FPhone,AcademicName,ClassName,SectionName,SubjectName,Max,Mark 
                ,Round((Mark) * 100/ (Max),1) as SubPer
                ,dense_rank() over(partition BY SUM(Max) order by SUM(Mark) DESC )  AS SubPos
                ,(CASE WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'A+'
                WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'A'
                WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'B'
                WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'C'
                WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'D'
                WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'E'
                ELSE 'FAIL' END ) AS Grade
 
                ,(CASE WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 90 THEN 'OutStanding'
                WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 80 THEN 'Excellent'
                WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 70 THEN 'Very Good'
                WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 60 THEN 'Good'
                WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 50 THEN 'Satisfactory'
                WHEN  Round((SUM(Mark)) * 100/ SUM([Max]),1) >= 40 THEN 'Work Hard'
                ELSE 'FAIL' END ) AS Remarks
 
                FROM tblSetMarks as fa
                            inner join tblSession as ses on fa.SessionID=ses.SessionID
                            inner join tblSetExam as st on fa.SetExamID=st.SetExamID
                            inner join tblStdReg as sr on fa.AdmissionNo=sr.AdmissionNo
                            inner join tblAssignSubjects as fh on fa.SubjectID=fh.ASID inner join tblDefSubject as df on fh.SubjectID=df.SubjectID
                            inner join tblAcademicYear as ay on sr.YearID=ay.YearID
                            inner join tblDefClass as dc on sr.ClassID=dc.ClassID
                            inner join tblDefSection as ds on sr.SectionID=ds.SectionID --where fa.SessionID=@SesId and fa.SetExamID=@EId and fa.ClassID=@CId and fa.SectionID=@SId                      
                          
                            group by SPic,SessionName,ExamType,fa.AdmissionNo,ReferenceNo,SName,FName,FPhone,AcademicName,ClassName,SectionName,SubjectName,Max,Mark order by AdmissionNo,SubjectName desc;

 

Posted 2 months ago Modified on 2 months ago

Hi smile,

Refer below sample query.

SQL

DECLARE @tblSetMarks AS TABLE(AdmissionNo VARCHAR(10),SubjectID VARCHAR(MAX),Obtain INT, Max INT)
INSERT INTO @tblSetMarks VALUES('R-000501','128',34,50)
INSERT INTO @tblSetMarks VALUES('R-000502','128',43 ,50)
INSERT INTO @tblSetMarks VALUES('R-000503','128',34,50)
INSERT INTO @tblSetMarks VALUES('R-000504','128',5 ,50)
INSERT INTO @tblSetMarks VALUES('R-000505','128',32,50)
INSERT INTO @tblSetMarks VALUES('R-000501','129',21,50)
INSERT INTO @tblSetMarks VALUES('R-000502','129',25,50)
INSERT INTO @tblSetMarks VALUES('R-000503','129',45,50)
INSERT INTO @tblSetMarks VALUES('R-000504','129',43,50)
INSERT INTO @tblSetMarks VALUES('R-000505','129',32,50)
INSERT INTO @tblSetMarks VALUES('R-000501','130',44,50)
INSERT INTO @tblSetMarks VALUES('R-000502','130',34,50)
INSERT INTO @tblSetMarks VALUES('R-000503','130',23,50)
INSERT INTO @tblSetMarks VALUES('R-000504','130',23,50)
INSERT INTO @tblSetMarks VALUES('R-000505','130',47,50)
SELECT AdmissionNo,SubjectID,Max, Obtain
,rank() over(partition BY SubjectID order by Obtain DESC)  AS Rank
FROM @tblSetMarks
ORDER BY AdmissionNo,SubjectID