dimanche 17 juillet 2016

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)

Aucun commentaire:

Enregistrer un commentaire