Home > Blockchain >  Find the position of a row in a given category along with total row in that category
Find the position of a row in a given category along with total row in that category

Time:01-10

I have the following data in a SQL Server table:

Guid    Token   Category    DateCreated
-------------------------------------------
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

I wan to get the result as shown below (get the position of a token within its category and total tokens in that category):

Token   Position#   OutOftotal (Where Category ='Category1' and Token='TK8')
TK8         8           11

Token   Position#   OutOftotal (Where Category ='Category1' and Token='TK5')
TK5         5           11

Token   Position#   OutOftotal (Where Category ='Category3' and Token='TK17')
TK17        2           4

CodePudding user response:

Here is an option using the window function 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' 

Results

Token   Position    OutofTotal
TK8     8           11

CodePudding user response:

Data

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');

use window functions such as row_number and max with over and Subquery as follows

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

  • Related