Get Rank based per row of a student score on a subject in sql server

Posted on

Question :

l have a sample data

// 


   USE [master]
GO
/****** Object:  Database [dbTest]    Script Date: 2019/03/01 12:20:40 ******/
CREATE DATABASE [dbTest]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'dbTest', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL14.SQLEXPRESSMSSQLDATAdbTest.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'dbTest_log', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL14.SQLEXPRESSMSSQLDATAdbTest_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [dbTest] SET COMPATIBILITY_LEVEL = 140
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [dbTest].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [dbTest] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [dbTest] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [dbTest] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [dbTest] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [dbTest] SET ARITHABORT OFF 
GO
ALTER DATABASE [dbTest] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [dbTest] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [dbTest] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [dbTest] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [dbTest] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [dbTest] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [dbTest] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [dbTest] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [dbTest] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [dbTest] SET  DISABLE_BROKER 
GO
ALTER DATABASE [dbTest] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [dbTest] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [dbTest] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [dbTest] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [dbTest] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [dbTest] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [dbTest] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [dbTest] SET RECOVERY SIMPLE 
GO
ALTER DATABASE [dbTest] SET  MULTI_USER 
GO
ALTER DATABASE [dbTest] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [dbTest] SET DB_CHAINING OFF 
GO
ALTER DATABASE [dbTest] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [dbTest] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO
ALTER DATABASE [dbTest] SET DELAYED_DURABILITY = DISABLED 
GO
ALTER DATABASE [dbTest] SET QUERY_STORE = OFF
GO
USE [dbTest]
GO
/****** Object:  Table [dbo].[tbl_result]    Script Date: 2019/03/01 12:20:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_result](
    [id] [int] NOT NULL,
    [student_id] [varchar](10) NOT NULL,
    [subject_id] [int] NOT NULL,
    [score] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (3, N'R135722F', 7112, 43)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (4, N'R135722F', 6118, 55)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (5, N'R135722F', 2076, 45)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (6, N'R135722F', 4402, 76)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (7, N'R135722F', 2234, 34)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (8, N'R134567Y', 6118, 65)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (9, N'R134567Y', 2076, 87)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (10, N'R134567Y', 4402, 43)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (11, N'R134567Y', 2234, 65)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (12, N'R134567Y', 1111, 34)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (13, N'R134567Y', 3454, 65)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (14, N'R134567Y', 3456, 87)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (15, N'R137634H', 7112, 98)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (16, N'R137634H', 6118, 54)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (17, N'R137634H', 2076, 45)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (18, N'R137634H', 4402, 33)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (19, N'R137634H', 2234, 65)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (20, N'R137634H', 2234, 65)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (21, N'R137634H', 1111, 12)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (22, N'R137634H', 3454, 54)
USE [master]
GO
ALTER DATABASE [dbTest] SET  READ_WRITE 
GO

//

l want to get by student_id all the subjects and rank based on subject

for example a student wrote subject 7112 l want rank of that particular subject
for that student and how many wrote that subject as out of

Example 1 . Ranking for subject code 7112

enter image description here

Example 2 . Ranking for subject 6118

enter image description here

Example 3 . Ranking for subject 2076

enter image description here

NB: a student can write one subject but can write many different but not writing twice per subject

What l want to achieve is grab subjects based on student id and get rank for each subject like when getting for a specific student report

enter image description here

or

enter image description here

What l tried

SELECT stu.* , 
(
SELECT TOP 1 + 
DENSE_RANK () OVER (PARTITION BY [tbl_result].score order by  
                [tbl_result].score DESC ) as ranking
  FROM
[dbo].[tbl_result]
Where [dbo].[tbl_result].[student_id] = stu.[student_id]
) As Rank 
FROM [dbo].[tbl_result] stu
Where stu.[student_id] = 'R135722F'

What l also assume/ think

l can use a cursor but l still new to them and dont want slow perfomance just by writting bad sql due to less knowledge

Why am getting wrong results

if l rank for subject getting all students l get the rank as

SELECT *  ,
    DENSE_RANK () OVER (ORDER BY [tbl_result].score  DESC ) as ranking
      FROM
    [dbo].[tbl_result] 
    Where subject_id = 7112

but when getting for student and the subjects he / she wrote with their rank am failing to do

Edit 1
Example 3 rank last two rows had rame rank of 2 for score 45
enter image description here

Answer :

Try something like

WITH cte AS (
SELECT student_id, subject, score, 
CAST(RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS CHAR(3))
    + ' out of ' +
    CAST(COUNT(student_id) OVER (PARTITION BY subject) AS CHAR(3)) AS [rank]
FROM table
-- ORDER BY student, subject
)
SELECT * /* subject, score, rank */
FROM cte
WHERE student_id = 'R135722F'

Leave a Reply

Your email address will not be published. Required fields are marked *