Que tal, espero esto te ayude.
Código SQL
[-]CREATE FUNCTION dbo.Maximo_Ventas()
RETURNS @table_resultado TABLE (IdEmpleado int, Primer_Nombre nvarchar(10), Monto_total money)
AS
BEGIN
DECLARE @TABLE TABLE (RowNumber int, IdEmpleado int, Primer_Nombre nvarchar(10), Monto_total money)
INSERT @TABLE
SELECT ROW_NUMBER() OVER(ORDER BY ROUND(SUM((UnitPrice -(UnitPrice*Discount)) * Quantity), 2) desc) AS RowNumber, e.EmployeeID, e.FirstName, ROUND(SUM((UnitPrice -(UnitPrice*Discount)) * Quantity), 2) AS Total_Orden
FROM Employees e
INNER JOIN Orders o on o.EmployeeID = e.EmployeeID
INNER JOIN [Order Details] od on od.OrderID = o.OrderID
GROUP BY e.EmployeeID,
e.FirstName
INSERT INTO @table_resultado
SELECT TOP 1 IdEmpleado, Primer_Nombre, Monto_total
FROM @TABLE
RETURN
END
GO
SELECT * FROM dbo.Maximo_Ventas()
Resultado
4 Margaret 232890.85
Saludos