Dynamic Row Count in SQL query

Question

Hi guys I'm trying to count the number of towers in a master property.. but i having problems resetting the count. I only have this code.

SELECT ProjectName,T.TowerName ,
           CONVERT(NVARCHAR(2),RIGHT('00'+CAST(rank() OVER (ORDER BY T.TowerID) AS VARCHAR(3)),2)) AS TowerID   
FROM MasterProperty AS MP 
INNER JOIN Tower AS T ON Mp.MasterPropertyID = T.MasterPropertyID

This is what got.

This what i need.

Gurwinder Singh · Accepted Answer · 2017-02-01 08:25:48Z

3

You can use row_number window function:

select
    t.*,
    row_number() over (partition by ProjectName order by TowerId) whatIwant
from your_table t;

edited Feb 1, 2017 at 8:25

answered Feb 1, 2017 at 8:14

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamic Row Count in SQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related