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 Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\dbTest.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
LOG ON
( NAME = N'dbTest_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\dbTest_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
Example 2 . Ranking for subject 6118
Example 3 . Ranking for subject 2076
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
or
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