CODEWITHSUNDEEP
sqlms-accesssubquerymax
Luke
Luke

Reputation: 11

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

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'
)

Upvotes: 0

Views: 481

Answers (2)

Parfait
Parfait

Reputation: 107632

As mentioned, you need to correlate the subquery to outer query. Be sure to use aliases between same named columns and exercise good practice in qualifying all columns with table names or aliases especially in 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
)

Note: If running in MS Access, remove the comment

Upvotes: 0

Malchar
Malchar

Reputation: 31

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

Upvotes: 1

Related Questions