Selecting the highest value in SQL

Question

I want to select the highest value, I have joined two tables and I want to be able to find the highest value as well as the persons first name.

I have my column called SalesYTD which is in my Sales.Salesperson table. And my Firstname column which is in my person.person table.

I want to find only the highest value in the SalesYTD table and display it along with the persons Firstname.

I've tried the following query:

SELECT FirstName, MAX(SalesYTD) AS YTD 
FROM Person.Person 
JOIN Sales.SalesPerson ON Person.Person.BusinessEntityID = Sales.SalesPerson.BusinessEntityID
GROUP BY FirstName, SalesYTD

Answer 1

A query has a SELECT. The answer to your question is to use TOP (1) and ORDER BY.

Presumably, the query looks like:

SELECT TOP (1) FirstName, SalesYTD
FROM Person p JOIN
     Sales.SalesPerson sp
     ON p.BusinessEntityID = sp.BusinessEntityID
ORDER BY SalesYTD DESC;