SQL serial number by group

Question

I need to have a data output that should generate a serial number by group. Consider the example below.

Name 
----
XXXX
XXXX
XXXX
YYYY
YYYY
ZZZZ
ZZZZ
ZZZZ

expected output:

SlNo  Name 
----  ----
1     XXXX
1     XXXX
1     XXXX
2     YYYY
2     YYYY
3     ZZZZ
3     ZZZZ
3     ZZZZ

tried using rownumber() but generates serial number within group

Answer 1

SELECT  *,
        DENSE_RANK() OVER (ORDER BY name)
FROM    mytable