Problem of Order By Clause

Question

I have a student marksheet table. like that..

SELECT * FROM MARKSHEET_TAB ORDER BY FULL_MARKS DESC

Reg_No	Marks_Obtain	Full_Marks	Roll_No
R1051	651	900	22
R1032	645	900	5
R1013	645	900	3
R1025	638	900	8
R1011	625	900	12

I want to display these records if those students have obtained same marks then rank sheet will generated based on their class roll no. that is..

Reg_No	Marks_Obtain	Full_Marks	Roll_No	Rank
R1051	651	900	22	1
R1013	645	900	3	2
R1032	645	900	5	3
R1025	638	900	8	4
R1011	625	900	12	5

Please solve this problem.

Jignesh Kumar · Answer

Hi Prakaash, Please find below query for your requrement. select Reg_no, Marks_obtain, Full_Marks,Roll_No, row_number() over( order by Marks_obtain desc , roll_no asc ) as rowId from Result In SQL there are three function to find rank, please have a look in below article where its clear which function does what ? https://www.c-sharpcorner.com/blogs/difference-between-rownumber-rank-denserank-in-sql-server Is your issuw Still Exists or Resolved?

Prakash Mondal · Answer

Hi! Mr. Kumar, Thank you so much. 1. select * from result order by marks_obtain desc , roll_no asc returns correct result. but the 2nd query 2. select Reg_no, Marks_obtain, Full_Marks,Roll_No, Dense_rank() over( order by Marks_obtain desc ) as rowId from Result :::: select Reg_no, Marks_obtain, Full_Marks,Roll_No, Dense_rank() over(order by Marks_obtain desc) as rowId from Result order by marks_obtain desc, roll_no asc display rowid duplicate values. Please help.

Jignesh Kumar · Answer

Hi prakash, You are right, its depend on your requirement.you can use first query. If my post help you then please mark answer.

Jignesh Kumar · Answer

Hi Prakash, Please find below queries to get your expected result select * from result order by marks_obtain desc , roll_no asc select Reg_no, Marks_obtain, Full_Marks,Roll_No, Dense_rank() over( order by Marks_obtain desc ) as rowId from Result

Laxmidhar Sahoo · Answer

hi jignes kis correct .If you have same roll in multiple times may be select Reg_no, Marks_obtain, Full_Marks,Roll_No, rank() over(PARTITION BY Roll_No order by Marks_obtain desc ) as rowId from Result