WITH LongDescription AS
    (SELECT MescID AS MescID  FROM
        (SELECT ktm.matnr AS MescID FROM dbo.ZE1MAKTM ktm
        WHERE ktm.matnr LIKE '%01.66%'
        INTERSECT SELECT TDNAME AS MescID FROM ZE1MTXLM
        WHERE len(tdname) = 14 ) AS Tbl
        WHERE isNumeric(RIGHT(MescID, 1)) = 1)
SELECT *  FROM (
SELECT ktm.matnr AS MescID, ktm.MAKTX AS ShortDesc, ram.MFRNR AS ManufNumber, ROW_NUMBER() OVER (ORDER BY ktm.matnr) AS RowNumber
FROM ZE1MAKTM ktm
INNER JOIN LongDescription r ON ktm.matnr = r.MescID
INNER JOIN ZE1MARAM ram ON ram.matnr = ktm.matnr
WHERE ram.matnr LIKE '%01.66%'
GROUP BY ktm.matnr, ram.MFRNR, ktm.MAKTX
) AS Tbl

WHERE  Tbl.RowNumber BETWEEN 1 AND 10