sql group by order by problem in C#

Hi try this and u can change the order by clause as per your need

DECLARE @tempTable AS TABLE
        (
          S_en INT ,
          S_P_name VARCHAR(64) ,
          S_supName VARCHAR(64) ,
          S_qty_avail INT ,
          S_Pur_rate INT ,
          S_batchno VARCHAR(64) ,
          S_Expdate DATE ,
          S_totalGross INT
        )

INSERT  INTO @tempTable
        ( S_en, S_P_name, S_supName, S_qty_avail, S_Pur_rate, S_batchno,
          S_Expdate, S_totalGross )
VALUES
        ( 1, 'crocin', 'aaa', 5, 10, 'b8', '01-Jul-2014', 22 ),
        ( 2, 'disprin', 'bbb', 8, 8, 'd8', '01-Jul-2020', 30 ),
        ( 3, 'crocin', 'Opening', 2, 0, 'aj9', '01-Apr-2020', 20 ),
        ( 4, 'combiflame', 'kkk', 4, 2, 's9', '01-Jul-2015', 15 ),
        ( 5, 'disprin', 'kkk', 3, 5, 'k9', '01-Jul-2021', 12 );

WITH temp AS(
SELECT
 
  S_P_name ,
 Max(S_supName) AS S_supName ,
  ( SELECT
      SUM(S_qty_avail)
    FROM
      @tempTable s
    WHERE
      s.S_P_name = r.S_P_name
    GROUP BY
      S_P_name
  ) AS S_qty_avail ,
  MIN(S_Pur_rate) AS S_Pur_rate
FROM
  @tempTable r
WHERE
  S_supName <> 'Opening'
GROUP BY
  S_P_name

)


SELECT   ROW_NUMBER() OVER(ORDER BY a.S_supName desc),
  S_P_name ,
  S_supName ,
  S_qty_avail ,
  (SELECT MIN(S_Pur_rate) OVER (PARTITION BY S_P_name) FROM @tempTable s WHERE s.S_supName=a.s_supname AND a.S_P_name = s.S_P_name)as S_Pur_rate
 
   FROM  temp a
   ORDER BY a.S_supName desc