CODEWITHSUNDEEP
sql-server
Baral
Baral

Reputation: 3141

SQL ROW_NUMBER LIKE Function

I would like to know if there is a way to do the following within a SQL Server script.

Let's say I have the following table

** To make things simple, IDs in example are INT. In my real scenario, these are UNIQUEIDENTIFIER

ParentId     ChildId   
-----------------------
101          201        
101          202       
101          203       
102          204        
102          205     
103          206       
103          207      
103          208        
103          209

I would like to query the table to get the following result.

So far I was able to get the ChildIndex column using the ROW_NUMBER() function. I am now struggling with the ParentIndex column...

ParentId     ChildId    ChildIndex    ParentIndex
---------------------------------------------------
101          201        1             1 
101          202        2             1
101          203        3             1
102          204        1             2
102          205        2             2
103          206        1             3
103          207        2             3
103          208        3             3
103          209        4             3

Here is my query so far

SELECT ParentId,
       ChildId,
       ROW_NUMBER() OVER ( PARTITION BY ParentId ORDER BY ParentId DESC ) AS ChildIndex
FROM   MyTable

Upvotes: 0

Views: 89

Answers (1)

John Woo
John Woo

Reputation: 263733

DENSE_RANK() is all you need.

DENSE_RANK() OVER (ORDER BY ParentId DESC) AS ParentIndex

Upvotes: 1

Related Questions