select g.CODE , count(*)-1 as C_NO , g.NAME from MyTable as g left join MyTable as o on g.CODE = o.CODE and g.NAME >= o.NAME group by g.CODE , g.NAME;