RANK() ,ROW_NUMBER(),DENSE_RANK( ),ntile( )

INSERT INTO table1 VALUES ('1','01')
INSERT INTO table1 VALUES ('1','02')
INSERT INTO table1 VALUES ('1','03')
INSERT INTO table1 VALUES ('2','01')
INSERT INTO table1 VALUES ('3','02')
INSERT INTO table1 VALUES ('3','03')
INSERT INTO table1 VALUES ('4','01')
INSERT INTO table1 VALUES ('4','02')
INSERT INTO table1 VALUES ('4','03')

SELECT * FROM [dbo].[table1]

SELECT SID, COUNT(DISTINCT CID ) AS SUM_CID FROM table1
GROUP BY sid
ORDER BY SUM_CID DESC ;

SELECT SID,COUNT(DISTINCT CID ) AS CON_CID, RANK()OVER (ORDER BY (COUNT(DISTINCT CID ))DESC) AS SUM_CID FROM table1
GROUP BY sid

SELECT SID,COUNT(DISTINCT CID ) AS CON_CID, DENSE_RANK()OVER (ORDER BY (COUNT(DISTINCT CID ))DESC) AS SUM_CID FROM table1
GROUP BY sid

SELECT SID,COUNT(DISTINCT CID ),ROW_NUMBER() OVER (ORDER BY (COUNT(DISTINCT CID ))DESC) AS SUM_CID from table1
GROUP BY sid

(SELECT SID,COUNT(DISTINCT CID ) AS CON_CID, ntile(3)OVER (ORDER BY (COUNT(DISTINCT CID ))DESC) AS SUM_CID FROM table1
GROUP BY sid)