/******  ******/
//Initialization data
 
create table employee (empid int, deptid int, salary decimal(10, 2))
insert into employee values(1, 10, 5500.00)
insert into employee values(2, 10, 4500.00)
insert into employee values(3, 20, 1900.00)
insert into employee values(4, 20, 4800.00)
insert into employee values(5, 40, 6500.00)
insert into employee values(6, 40, 14500.00)
insert into employee values(7, 40, 44500.00)
insert into employee values(8, 50, 6500.00)
insert into employee values(9, 50, 7500.00)

The data is displayed as:

empid       deptid      salary
----------- ----------- ---------------------------------------
1           10          5500.00
2           10          4500.00
3           20          1900.00
4           20          4800.00
5           40          6500.00
6           40          14500.00
7           40          44500.00
8           50          6500.00
9           50          7500.00

Demand: display the salary grade of each department by department grouping.

SQL script:

SELECT *, Row_Number() OVER (partition by deptid ORDER BY salary desc) rank 
FROM employee

empid       deptid      salary                                  rank
----------- ----------- --------------------------------------- --------------------
1           10          5500.00                                 1
2           10          4500.00                                 2
4           20          4800.00                                 1
3           20          1900.00                                 2
7           40          44500.00                                1
6           40          14500.00                                2
5           40          6500.00                                 3
9           50          7500.00                                 1
8           50          6500.00                                 2

SQL script:

SELECT *
FROM
    (SELECT *, Row_Number() OVER (partition by deptid ORDER BY salary desc) R
     FROM employee) B
WHERE B.R =1
 
#Show only the highest wage in the Department
/******  ******/

 select *
  from (
  select name, discipline, score, ROW_NUMBER() over (PARTITION by discipline order by score desc) as num
  from [XSBOOK].[dbo].[XKCJ]) T
  Where t.num < = 3 order by T. discipline
  
name subject score num
---------- ---------- ----------- --------------------
Li Si mathematics 98 1
Lisi mathematics 95 2
Zhang San mathematics 93 3
Zhang San English 92 1
Zhang San English 92 2
Li Si English 90 3
Lisi 85 1
Li Si language 81 2
Zhang San Chinese 80 3

Syntax form: ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)

Explanation: according to the COL1 group, the internal group is sorted according to COL2, and the value calculated by this function represents the sequence number (consecutive and unique within the group) after the internal sorting of each group.