Selecting max value from nested query

Question

I have a nested query which returns the totalFare of all passengers grouped by the different passenger types.

SELECT PASSNGR.type , sum(FARE.fee) AS totalFare 
  FROM Passenger AS PASSNGR 
  JOIN Fare AS FARE ON (PASSNGR.type = FARE.type) 
  GROUP BY PASSNGR.type 
  ORDER BY totalFare DESC

I am trying to understand how to select the row with the Max(totalFare). I have looked around but most are selecting the maximum from a defined table, where as I am using a nested query to generate the totalFare per passenger type.

I tried to nest another Select MAX(totalFare) before the initial select, but that only returns the value of the max not the type as well.

SELECT MAX(totalFare) 
  FROM (SELECT PASSNGR.type , sum(FARE.fee) AS totalFare 
          FROM Passenger AS PASSNGR 
          JOIN Fare AS FARE ON (PASSNGR.type = FARE.type) 
          GROUP BY PASSNGR.type ORDER BY totalFare)

The [tag:greatest-n-per-group] tag is dedicated to questions like this. What would you want as the answer if the type 1 total fares is $5000, and the type 2 total fares is also $5000? — Jonathan Leffler, Nov 05 '17 at 23:22

score 3 · Accepted Answer · answered Nov 05 '17 at 23:27

Does this do what you want?

SELECT p.type, sum(f.fee) AS totalFare 
FROM Passenger p JOIN
     Fare f
     ON p.type = f.type
GROUP BY p.type 
ORDER BY totalFare DESC
FETCH FIRST 1 ROW ONLY;

I should note that the JOIN is not necessary. You can do;

SELECT f.type, sum(f.fee) AS totalFare 
FROM Fare f
GROUP BY f.type 
ORDER BY totalFare DESC
FETCH FIRST 1 ROW ONLY;

Selecting max value from nested query

1 Answers1