SQL analysis function over Partition

Set the atable table as follows:
A B C
Aa sx 1000
Aa zh 900
BB, JJ 1100
Bb ZH 500
Bb Gg 400
Bb WD 1, 300
Bb Sh 1, 150
CC sh 1500

Convert to a btable as follows:
A B C
Aa sx 1000
Aa zh 900
BB, JJ 1100
Bb ZH 500
Bb Gg 400
CC sh 1500

That is, less than three pieces of data are taken out, more than three pieces of data are taken, and three pieces are filtered back.

 

Select T. * From
( Select Atable. * , Row_number () Over (Partition By A Order By C Desc ) Rn From Atable) T
Where Rn <= 3