Rank of T-SQL function

There is a SQL written question like this:

The table information is known as follows:

Department (Depid, Depname), Depid department number,depname Department name

Student (Stuid, Name, depid) student number, name, department number

Score (STUID, category, score) student code, subject, score

Find the highest score for each system, and by department number, the student number in ascending order, requires sequential output of the following information:

Department number, name, student number, name, total score

Test data:

Use [test]go/****** object:table [dbo].    [Score] Script date:05/11/2015 23:16:23 ******/set ansi_nulls ongoset quoted_identifier ongoset ansi_padding ONGOCREATE TABLE [db O]. [Score] ([stuid] [int] NOT NULL, [category] [varchar] (+) not NULL, [score] [int] is not NULL) On [Primary]goset ansi_padding offgoinsert [dbo]. [Score] ([Stuid], [category], [score]) VALUES (1, N ' English ', +) INSERT [dbo]. [Score] ([Stuid], [category], [score]) VALUES (2, N ' math ', +) INSERT [dbo]. [Score] ([Stuid], [category], [score]) VALUES (1, N ' math ', +) INSERT [dbo]. [Score] ([Stuid], [category], [score]) VALUES (2, N ' English ', the ") INSERT [dbo]. [Score] ([Stuid], [category], [score]) VALUES (3, N ' English ', Bayi) INSERT [dbo]. [Score] ([Stuid], [category], [score]) VALUES (3, N ' math ', [+]) INSERT [dbo]. [Score] ([Stuid], [category], [score]) VALUES (4, N ' math ', A.) INSERT [dbo]. [Score] ([Stuid], [category], [score]) VALUES (4, N ' English ', A.) INSERT [dbo]. [Score] ([Stuid], [category], [score]) VALUES (5, N ' English ', A.) INSERT [dbo]. [Score] ([Stuid], [Category], [score]) VALUES (6, N ' English ', and ") INSERT [dbo]. [Score] ([Stuid], [category], [score]) VALUES (7, N ' English ', a) INSERT [dbo]. [Score] ([Stuid], [category], [score]) VALUES (8, N ' English ', $) INSERT [dbo]. [Score] ([Stuid], [category], [score]) VALUES (9, N ' English ', $) INSERT [dbo]. [Score] ([Stuid], [category], [score]) VALUES (5, N ' math ', and all) INSERT [dbo]. [Score] ([Stuid], [category], [score]) VALUES (6, N ' math ', ",") INSERT [dbo]. [Score] ([Stuid], [category], [score]) VALUES (7, N ' math ', +) INSERT [dbo]. [Score] ([Stuid], [category], [score]) VALUES (8, N ' math ', Bayi) INSERT [dbo]. [Score] ([Stuid], [category], [score]) VALUES (9, N ' math ',/******) object:table [dbo].    [Department] Script date:05/11/2015 23:16:23 ******/set ansi_nulls ongoset quoted_identifier ongoset ansi_padding ONGOCREATE TABLE [db O]. [Department]  ([depid] [int] IDENTITY () not NULL, [depname] [varchar] (a) Not null,primary KEY CLUSTERED ([depid] ASC) with (Pad_index = off, Statistics_norecompute = off, Ignore_dup_key =OFF, allow_row_locks = on, allow_page_locks = on) on [PRIMARY]) on [Primary]goset ansi_padding offgoset Identity_insert [dbo]. [Department] ONINSERT [dbo]. [Department] ([Depid], [Depname]) VALUES (1, N ' computer ') INSERT [dbo]. [Department] ([Depid], [Depname]) VALUES (2, N ' mobs ') INSERT [dbo]. [Department] ([Depid], [Depname]) VALUES (3, N ' math ') SET identity_insert [dbo]. [Department] off/****** object:table [dbo].    [Student] Script date:05/11/2015 23:16:23 ******/set ansi_nulls ongoset quoted_identifier ongoset ansi_padding ONGOCREATE TABLE [db O]. [Student] ([stuid] [int] IDENTITY () not NULL, [stuname] [varchar] (a) not NULL, [DeptID] [int.] not null,primary KEY CLU Stered ([Stuid] ASC) with (Pad_index = off, Statistics_norecompute = off, Ignore_dup_key = off, allow_row_locks = On , allow_page_locks = on) on [PRIMARY]) on [Primary]goset ansi_padding offgoset identity_insert [dbo]. [Student] ONINSERT [dbo]. [Student] ([Stuid], [Stuname], [DeptID]) VALUES (1, N ' computer Zhang San ', 1) INSERT [dbo]. [Student] ([Stuid], [Stuname], [DeptID]) VALUES (2, N ' Computer John Doe ', 1) INSERT [dbo]. [Student] ([Stuid], [Stuname], [DeptID]) VALUES (3, N ' computer Harry ', 1) INSERT [dbo]. [Student] ([Stuid], [Stuname], [DeptID]) VALUES (4, N ' Biology Amy ', 2) INSERT [dbo]. [Student] ([Stuid], [Stuname], [DeptID]) VALUES (5, N ' Creature kity ', 2) INSERT [dbo]. [Student] ([Stuid], [Stuname], [DeptID]) VALUES (6, N ' Creature Lucky ', 2) INSERT [dbo]. [Student] ([Stuid], [Stuname], [DeptID]) VALUES (7, N ' Math _yiming ', 3) INSERT [dbo]. [Student] ([Stuid], [Stuname], [DeptID]) VALUES (8, N ' Math _haoxue ', 3) INSERT [dbo]. [Student] ([Stuid], [Stuname], [DeptID]) VALUES (9, N ' Math _wuyong ', 3) SET identity_insert [dbo]. [Student] off/****** Object:default [df__departmen__depna__5441852a] Script date:05/11/2015 23:16:23 ******/ALTER TABLE [dbo]. [Department] ADD DEFAULT (") for [depname]go/****** Object:default [df__score__category__5ebf139d] Script date:05/11/2015 23:16 : ******/alter TABLE [dbo]. [Score] ADD DEFAULT (") for [category]go/****** Object: Default [Df__score__score__5fb337d6] Script date:05/11/2015 23:16:23 ******/alter TABLE [dbo]. [Score] ADD DEFAULT ((0)) for [score]go/****** Object:default [Df__student__stuname__59063a47] Script date:05/11/2015 23:16 : ******/alter TABLE [dbo]. [Student] ADD DEFAULT (") for [stuname]go/****** Object:foreignkey [Fk__student__deptid__59fa5e80] Script date:05/11/2015 23 : 16:23 ******/alter TABLE [dbo].  [Student] With CHECK ADD FOREIGN KEY ([DeptID]) REFERENCES [dbo]. [Department] ([Depid]) GO

SQL query statement:

with T1 as (select B.stuid,sum (A.score) as score from dbo. Score a LEFT JOIN dbo. Student b on a.stuid = B.stuidgroup by B.stuid), T2 as (select A.stuid,a.stuname,a.deptid,b.depname from dbo. Student a LEFT join Department b in A.deptid = B.depid), t3 as (select Rank () over (partition by DeptID ORDER BY score Desc) As Rowid,t2.stuid,t2.stuname,t2.deptid,t2.depname,t1.score from T1 left join t2 on t1.stuid = t2.stuid) Select Stuid,stuna Me,deptid,depname,score from t3 where RowId = 1

Query Result:

Unlike Row_rumber, rank takes into account the same sort field values in the over clause. If you replace rank with row_number, there will be 1 records with a deptid of 2.

Rank of T-SQL function