Groupwise Sequence Number Generation , Rank and Row Number Functions

Rank and Row_Number Functions

In the below table I have couple of duplicate image numbers, I need to assign sequence number for duplicate image numbers. For example if there are three duplicate image numbers then sequence should be 1, 2, 3 for those three and again the sequence will start from 1 for next image number.

ROW_NUMBER : Returns a unique number for each row starting with 1. For rows that have duplicate values,numbers are arbitarily assigned.

Rank : Assigns a unique number for each row starting with 1,except for rows that have duplicate values,in which case the same ranking is assigned and a gap appears in the sequence for each duplicate ranking.

CREATE TABLE #tmp(IdNo INT, Image_No VARCHAR(10), Seq_No INT)
GO
INSERT #tmp (IdNo, Image_No) VALUES(1,'FS1')
INSERT #tmp (IdNo, Image_No) VALUES(2,'FS1')
INSERT #tmp (IdNo, Image_No) VALUES(3,'FS2')
INSERT #tmp (IdNo, Image_No) VALUES(4,'FS3')
INSERT #tmp (IdNo, Image_No) VALUES(5,'FS3')
INSERT #tmp (IdNo, Image_No) VALUES(6,'FS3')
INSERT #tmp (IdNo, Image_No) VALUES(7,'FS2')
GO
Below query used to return sequence number without using Row_Number and Rank Functions

SELECT IdNo, Image_No, (select count(*)+1 from #tmp b where IdNo < a.IdNo and a.Image_No = b.Image_No) FROM #tmp a order by Image_No

Rank Function

SELECT IdNo, Image_No,  RANK() OVER(PARTITION BY Image_No  ORDER BY idno) as 'Rank'  FROM #tmp

Row_Number

SELECT IdNo, Image_No,  ROW_NUMBER() OVER(PARTITION BY Image_No  ORDER BY idno) as 'RowNumber'  FROM #tmp