How to Use With CTE in MySql

<pre>
     ;WITH CTECostCentre AS
    (
        SELECT *,COUNT(t1.CostCentreID) OVER()  COUNT FROM
        (
            SELECT ROW_NUMBER() OVER (ORDER BY
                CASE WHEN SortType = &#39;DESC&#39; THEN
                    CASE
                        WHEN SortExpression = &#39;CategoryName&#39; THEN CategoryName
                    END
                END DESC
            ) AS ROW,te
            FROM  categorymaster C
            WHERE (
                     (IsSearchByText = 1 AND (SearchText IS NULL OR LEN(SearchText)=0)
                     OR REPLACE(C.CostCentreName, &#39; &#39;, &#39;&#39;) LIKE &#39;%&#39; + REPLACE(SearchText,&#39; &#39;,&#39;&#39;) + &#39;%&#39;
                     OR REPLACE(CG.CategoryName, &#39; &#39;, &#39;&#39;) LIKE &#39;%&#39; + REPLACE(SearchText,&#39; &#39;,&#39;&#39;) + &#39;%&#39;

                     )
                   )
                ) AS t1
    )

    SELECT * FROM CTECostCentre WHERE ROW BETWEEN FromIndex AND ToIndex
</pre>