Hmm. I had intended it as a general question. However, I acknowledge your point about schema having influences. So, for a need of a handy test data, I turned to AdventureWorks2008R2 database.

Here's the queries to try out:

1) Get sales person's best sales ever and detail about this sales:

SELECT

o.SalesOrderID,

o.OrderDate,

o.AccountNumber,

o.TotalDue,

o.SalesPersonID,

p.FirstName,

p.LastName

FROM Sales.SalesOrderHeader AS o

INNER JOIN Person.Person AS p

ON o.SalesPersonID = p.BusinessEntityID

LEFT JOIN Sales.SalesOrderHeader AS m

ON o.SalesPersonID = m.SalesPersonID

AND o.TotalDue < m.TotalDue

WHERE m.TotalDue IS NULL;

2) Get the best sales for each month for different years. Note that because of functions applied in the join criteria, the query is non-sargable so I expect it to perform badly. Normally, if that was crucial for business, I probably would have added indexed computed field to keep the year and month separate.

SELECT

o.OrderDate,

o.AccountNumber,

o.TotalDue,

o.SalesPersonID,

p.FirstName,

p.LastName

FROM Sales.SalesOrderHeader AS o

INNER JOIN Person.Person AS p

ON o.SalesPersonID = p.BusinessEntityID

LEFT JOIN Sales.SalesOrderHeader AS m

ON YEAR(o.OrderDate) = YEAR(m.OrderDate)

AND MONTH(o.OrderDate) = MONTH(m.OrderDate)

AND o.TotalDue < m.TotalDue

WHERE m.TotalDue IS NULL;

There were no modifications to the AdventureWorks2008R2 database. Hopefully, that'll provide you with enough sample data.

Thanks again!