Thursday, June 24, 2010

SQL Server ROW_NUMBER

Suppose there is a table Students having the columns StudName, RollNo, TotalMarks.Write a query (in SQL Server) that returns RollNo, Student Name and Total Marks along with the position (based on marks) of the students in the class.
For eg. Suppose table has following records:


The output should be

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

1 comment:

  1. SELECT RollNo, StudName, TotalMarks, Dense_Rank() OVER (ORDER BY TotalMarks DESC) AS StudPosition FROM Students ORDER BY StudPosition

    SELECT RollNo, StudName, TotalMarks, Rank() OVER (ORDER BY TotalMarks DESC) AS StudPosition FROM Students ORDER BY StudPosition

    By
    Neha Rohatgi, Satish, Navdeep Singh, Tulika

    ReplyDelete