How do I display only one result (the highest) with SQL query? (Beginner)-CodePudding

I need help making the following query only display one result, the one with the MAX Procurement Rate.

Currently the query works, but displays all results not just the one with the output of the MAX function

SELECT SalesPeople.SalesPersonID, FirstName, LastName, Region, SalesRevenueYear1, ProcurementCost
FROM ProductRevenueAndCosts 
INNER JOIN SalesPeople 
    ON ProductRevenueAndCosts.SalesPersonID = SalesPeople.SalesPersonID
WHERE SalesPeople.Region = 'Central' AND (
    SELECT MAX (ProcurementCost)
    FROM ProductRevenueAndCosts
    WHERE SalesPeople.Region = 'Central'
)

CodePudding user response：

If you add a LIMIT 1 clause at the end of your SQL, then only the first record will be shown. If you add an ORDER BY column_name, then the results will be ordered by that column. Using these two together is a quick way to get the max or min without having to worry about aggregate functions. https://www.w3schools.com/mysql/mysql_limit.asp Otherwise, you can try aggregating the results with a max function: https://www.w3schools.com/mysql/mysql_min_max.asp

CodePudding user response：

As mentioned, you need to correlate the subquery to outer query. Be sure to use aliases between same named columns and always recommended to qualify all columns table names or aliases with any JOIN queries:

SELECT sp.SalesPersonID, sp.FirstName, sp.LastName, sp.Region, sp.SalesRevenueYear1, 
       prc.ProcurementCost
FROM ProductRevenueAndCosts prc
INNER JOIN SalesPeople sp
    ON prc.SalesPersonID = prc.SalesPersonID
WHERE sp.Region = 'Central' 
  AND prc.ProcurementCost = (                   -- CORRELATE OUTER QUERY WITH SUBQUERY
    SELECT MAX (ProcurementCost)
    FROM ProductRevenueAndCosts
    WHERE SalesPeople.Region = 'Central'
)

^{Note: If running in MS Access, remove the comment}