For eg. Suppose table has following records:
INSERT INTO [ASPState].[dbo].[Students] ([StudName],[RollNo],[TotalMarks]) VALUES ('212094', 'X', 600)
INSERT INTO [ASPState].[dbo].[Students] ([StudName],[RollNo],[TotalMarks]) VALUES ('212095','Y', 540)
INSERT INTO [ASPState].[dbo].[Students] ([StudName],[RollNo],[TotalMarks]) VALUES ('212096','Z', 580)
INSERT INTO [ASPState].[dbo].[Students] ([StudName],[RollNo],[TotalMarks]) VALUES ('212097','A', 200)
INSERT INTO [ASPState].[dbo].[Students] ([StudName],[RollNo],[TotalMarks]) VALUES ('212098', 'B', 620)
Answer
SELECT *, ROW_NUMBER() OVER (ORDER BY TotalMarks DESC) AS StudPosition FROM Students

SELECT RollNo, StudName, TotalMarks, Dense_Rank() OVER (ORDER BY TotalMarks DESC) AS StudPosition FROM Students ORDER BY StudPosition
ReplyDeleteSELECT RollNo, StudName, TotalMarks, Rank() OVER (ORDER BY TotalMarks DESC) AS StudPosition FROM Students ORDER BY StudPosition
By
Neha Rohatgi, Satish, Navdeep Singh, Tulika