Problem of Order By Clause

Prakash Mondal

7y958

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.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Kumar7y ago
Accepted 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?
Jignesh Kumar7y ago
Hi prakash, You are right, its depend on your requirement.you can use first query. If my post help you then please mark answer.
Laxmidhar Sahoo7y ago
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
Prakash Mondal7y ago
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 Kumar7y ago
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