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