CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Alvie
Alvie

Reputation: 29

Dynamic Row Count in SQL query

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 is what got.

This what i need.

This what i need

Upvotes: 0

Views: 118

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39467

You can use row_number window function:

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

Upvotes: 3

Related Questions