Order records based on value

Michael M.M. D'Angelo 176 Reputation points
2023-08-26T20:47:03.59+00:00

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.

User's image

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,702 questions
0 comments No comments
{count} votes

Accepted answer
  1. Bruce (SqlWork.com) 55,601 Reputation points
    2023-08-26T21:16:43.28+00:00

    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 
    
    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 100.9K Reputation points MVP
    2023-08-26T21:22:04.0433333+00:00

    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.

    0 comments No comments