SELECT
obj.MEM_ID,
obj.count,
CASE
WHEN @rowtotal = obj.count THEN
@rownum
WHEN @rowtotal := obj.count THEN
@rownum :=@rownum + 1
WHEN @rowtotal = 0 THEN
@rownum :=@rownum + 1
END AS rownum
FROM
(
SELECT
c.MEM_ID,
count(*) count
FROM
T_CIRCLE_INTEREST c
WHERE
c.DELETED = 'N'
AND c.MEM_ID_ED IN (
SELECT
MEM_ID
FROM
T_TEACHER t
WHERE
t.DELETED = 'N'
)
GROUP BY
c.MEM_ID
) AS obj,
(SELECT @rownum := 0 ,@rowtotal := NULL) r
ORDER BY obj.count desc
obj.MEM_ID,
obj.count,
CASE
WHEN @rowtotal = obj.count THEN
@rownum
WHEN @rowtotal := obj.count THEN
@rownum :=@rownum + 1
WHEN @rowtotal = 0 THEN
@rownum :=@rownum + 1
END AS rownum
FROM
(
SELECT
c.MEM_ID,
count(*) count
FROM
T_CIRCLE_INTEREST c
WHERE
c.DELETED = 'N'
AND c.MEM_ID_ED IN (
SELECT
MEM_ID
FROM
T_TEACHER t
WHERE
t.DELETED = 'N'
)
GROUP BY
c.MEM_ID
) AS obj,
(SELECT @rownum := 0 ,@rowtotal := NULL) r
ORDER BY obj.count desc