I'm trying to write an SQL query to return information including product code, yearly sales revenues, costs, sales people information from two different tables. I need to return the product ID information for the product with the lowest'onboarding cost' for the 'north' region.

I have used WHERE Region = 'North' to just get the product info for the North region, and ORDER BY onboarding cost; to sort this low to high and find the product with the lowest cost. Is there a way of just returning the product with the lowest onboarding cost for the north region?

CodePudding user response：

In the WHERE clause you could add:

... AND onboarding cost=(SELECT MAX(onboarding cost) FROM ... WHERE region='North' ...)

 SELECT 
 ProductRevenueAndCosts.ProductID, 
 ProductRevenueAndCosts.SalesRevenueYear1, 
 ProductRevenueAndCosts.SalesRevenueYear2,  
 ProductRevenueAndCosts.OperationalCostsYear1, 
 ProductRevenueAndCosts.OperationalCostsYear2, 
 ProductRevenueAndCosts.OnboardingCost, 
 SalesPeople.FirstName, 
 SalesPeople.LastName, 
 SalesPeople.Region
FROM SalesPeople INNER JOIN ProductRevenueAndCosts ON 
SalesPeople.SalesPersonID = ProductRevenueAndCosts.SalesPersonID
WHERE SalesPeople.Region = "North" 
AND ProductRevenueAndCosts.OnboardingCost=(
   SELECT MAX(ProductRevenueAndCosts.OnboardingCost)
   FROM SalesPeople INNER JOIN ProductRevenueAndCosts ON 
   SalesPeople.SalesPersonID = ProductRevenueAndCosts.SalesPersonID
   WHERE SalesPeople.Region = "North"
)
ORDER BY ProductRevenueAndCosts.Onboardingcost DESC -- this is obsolete
;

But this will eventually return more than one result - in case that more than one product has the same highest onboaring cost.

CodePudding user response：

Yes, you need to limit the number of records, like this:

MySQL, PostgreSQL

SELECT 
 ProductRevenueAndCosts.ProductID, 
 ProductRevenueAndCosts.SalesRevenueYear1, 
 ProductRevenueAndCosts.SalesRevenueYear2,  
 ProductRevenueAndCosts.OperationalCostsYear1, 
 ProductRevenueAndCosts.OperationalCostsYear2, 
 ProductRevenueAndCosts.OnboardingCost, 
 SalesPeople.FirstName, 
 SalesPeople.LastName, 
 SalesPeople.Region
FROM SalesPeople INNER JOIN ProductRevenueAndCosts ON 
SalesPeople.SalesPersonID = ProductRevenueAndCosts.SalesPersonID
WHERE SalesPeople.Region = "North"
ORDER BY ProductRevenueAndCosts.Onboardingcost DESC
LIMIT 0, 1;

Where 0 is the starting index (first row) and 1 is the number of records you want to get.

SQL Server

SELECT TOP 1
 ProductRevenueAndCosts.ProductID, 
 ProductRevenueAndCosts.SalesRevenueYear1, 
 ProductRevenueAndCosts.SalesRevenueYear2,  
 ProductRevenueAndCosts.OperationalCostsYear1, 
 ProductRevenueAndCosts.OperationalCostsYear2, 
 ProductRevenueAndCosts.OnboardingCost, 
 SalesPeople.FirstName, 
 SalesPeople.LastName, 
 SalesPeople.Region
FROM SalesPeople INNER JOIN ProductRevenueAndCosts ON 
SalesPeople.SalesPersonID = ProductRevenueAndCosts.SalesPersonID
WHERE SalesPeople.Region = "North"
ORDER BY ProductRevenueAndCosts.Onboardingcost DESC;

Where TOP 1 tells the RDBMS that you are interested only in the very first row.

Oracle

SELECT 
 ProductRevenueAndCosts.ProductID, 
 ProductRevenueAndCosts.SalesRevenueYear1, 
 ProductRevenueAndCosts.SalesRevenueYear2,  
 ProductRevenueAndCosts.OperationalCostsYear1, 
 ProductRevenueAndCosts.OperationalCostsYear2, 
 ProductRevenueAndCosts.OnboardingCost, 
 SalesPeople.FirstName, 
 SalesPeople.LastName, 
 SalesPeople.Region
FROM SalesPeople INNER JOIN ProductRevenueAndCosts ON 
SalesPeople.SalesPersonID = ProductRevenueAndCosts.SalesPersonID
WHERE SalesPeople.Region = "North"
ORDER BY ProductRevenueAndCosts.Onboardingcost DESC
OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;