Xyno Xyno - 5 months ago 22
SQL Question

Counting rows sql server

I have a problem with counting rows. This is my procedure :

DECLARE @result table(id int,latitude float, longitude float)
Declare @z int = 1
while (@z <5)
BEGIN
INSERT INTO
@result
SELECT
id,
loc.STPointN(@z).Lat as lat,
loc.STPointN(@z).Long as long
FROM test6
SET @z = @z+1
END
Select * from @result
ORDER BY id


Results:

ID lat long
1 16,71175 52,689702
1 17,008381 52,247983
2 17,228107 52,689702
2 17,008381 42,247983
2 16,71175 42,689702


I want to count rows with same ID, somthing like :

ID lat long count
1 16,71175 52,689702 1
1 17,008381 52,247983 2
2 17,228107 52,689702 1
2 17,008381 42,247983 2
2 16,71175 42,689702 3


Any advice ? (Sorry for english)

Answer

You need to apply RowNumber window function like below

select
id,
lat,
long,
row_number() over(partition by id order by id) as countt
from 
yourtable

In the above code countt per id you get is not deterministic,if you want specific id column to have same countt,you need to order by unique value like

 row_number() over(partition by id order by lat) as countt
Comments