Hi i got the solution it will be like this..Thanks all to help me
WITH CTE AS (
SELECT SUBSTRING(code,2,2) as c_code, ProdName, ROW_NUMBER() OVER(PARTITION BY SUBSTRING(code,2,2)ORDER BY SERIALNUMBER,PRODUCTNUMBER,SOURCE) AS RowNumber
FROM Productdetail
)
SELECT * INTO #ProdDetail FROM CTE WHERE RowNumber =1
This will not change the order but gives the record on the top that what i was needed