Order records based on value

Question

Please I have the below table that stores students and their score

Script:

CREATE TABLE [dbo].[ClassScores](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Student] [nvarchar](50) NOT NULL,
	[TotalScore] [float] NOT NULL
 CONSTRAINT [PK_AAA] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[ClassScores] ON 
GO
INSERT [dbo].[ClassScores] ([Id], [Student], [TotalScore]) VALUES (1, N'Michael Tuscar', 72)
GO
INSERT [dbo].[ClassScores] ([Id], [Student], [TotalScore]) VALUES (2, N'Ernest Boyd', 57)
GO
INSERT [dbo].[ClassScores] ([Id], [Student], [TotalScore]) VALUES (3, N'Sarah Takre', 92)
GO
INSERT [dbo].[ClassScores] ([Id], [Student], [TotalScore]) VALUES (4, N'Moses Doyle', 57)
GO
INSERT [dbo].[ClassScores] ([Id], [Student], [TotalScore]) VALUES (5, N'Nick Shaw', 57)
GO
INSERT [dbo].[ClassScores] ([Id], [Student], [TotalScore]) VALUES (6, N'Francis Cool', 40)
GO
SET IDENTITY_INSERT [dbo].[ClassScores] OFF
GO

I need to create a view to include another column Position that will order the records based on the TotalScore such that the highest score becomes 1st as below, if there are similar marks then they will all have the same position.

Answer 1

use row_number() to calc position of score

select c.*, p.Position
from ClassScores c
join (
    select 
       TotalScore,
       row_number() over(order by TotalScore desc) as Position
    from ClassScores
    group by TotalScore
) p
    on p.TotalScore = c.TotalScore
order by p.Position 

Answer 2

Rather than using the row_number function suggested by Bruce, you need to use dense_rank in your particular case:

CREATE VIEW MyView AS 
    SELECT Id, Student, TotalScore, Position = dense_rank (ORDER BY TotalScore)
    FROM   tbl

Note that the view itself is by definition an unordered object.