ROW_NUMBER

You can partition the table returned to you. Each item in the partition with be assigned a number from 1 to the length of the partition. Aka the first record will be 1, the second 2.

SELECT 
	ColName1,
	ColName2,
	ColName3,
	ROWNUMBER() OVER (PARTITION BY ColName1 ORDER BY ColName1, ColName2) AS RankCol
FROM SchemaName.TableName
	ORDER BY ColName1, ColName2

Note: using PARTITION BY ColName1 is optional