以下是使用窗口函数sum() over()的选项

with cte as (
Select *
      ,[Position]   = sum(1) over (partition by Category order by [DateCreated])
      ,[OutofTotal] = sum(1) over (partition by Category)
 From  YourTable
 Where Category ='Category1' -- Optional but STRONGLY suggested
)
Select [Token]   
      ,[Position] 
      ,[OutofTotal]
 From cte 
 Where Category ='Category1' 
   and Token='TK8'

• • 结果**

Token   Position    OutofTotal
TK8     8           11

数据

CREATE TABLE mytable(
   Guid        VARCHAR(100) NOT NULL 
  ,Token       VARCHAR(100) NOT NULL
  ,Category    VARCHAR(100) NOT NULL
  ,DateCreated DATETIME  NOT NULL

);
INSERT INTO mytable
(Guid,Token,Category,DateCreated) VALUES 
('GUID1','TK1','Category1','1/9/2023 21:50'),
('GUID2','TK2','Category1','1/10/2023 21:51'),
('GUID3','TK3','Category1','1/11/2023 21:52'),
('GUID4','TK4','Category1','1/12/2023 21:53'),
('GUID5','TK5','Category1','1/13/2023 21:54'),
('GUID6','TK6','Category1','1/14/2023 21:55'),
('GUID7','TK7','Category1','1/15/2023 21:56'),
('GUID8','TK8','Category1','1/16/2023 21:57'),
('GUID9','TK9','Category1','1/17/2023 21:58'),
('GUID10','TK10','Category1','1/18/2023 21:59'),
('GUID11','TK11','Category1','1/19/2023 22:00'),
('GUID12','TK12','Category2','1/20/2023 22:01'),
('GUID13','TK13','Category2','1/21/2023 22:02'),
('GUID14','TK14','Category2','1/22/2023 22:03'),
('GUID15','TK15','Category2','1/23/2023 22:04'),
('GUID16','TK16','Category3','1/24/2023 22:05'),
('GUID17','TK17','Category3','1/25/2023 22:06'),
('GUID18','TK18','Category3','1/26/2023 22:07'),
('GUID19','TK19','Category3','1/27/2023 22:08');

使用窗口函数，如row_number、max with over和Subquery，如下所示

SELECT Token,
       Position,
       Max(Position)
         OVER(
           PARTITION by Category ) OutOftotal
FROM   (select Token,
               Category,
               DateCreated,
               row_number()
                 over (
                   PARTITION by Category
                   order by DateCreated ) Position
        FROM   mytable) M

dbfiddle