How can i find the row with the max value in sql?-CodePudding

I need to get which guest spent the most in a hotel throughout their lifetime. The room table has the price per individual room.

SELECT g.guest_id, g.name, g.email, sum(room_price) sumcost
FROM booking b
INNER JOIN guest g ON b.guest_id = g.guest_id
INNER JOIN room r ON r.room_id = b.room_id
GROUP BY g.guest_id;

The above query gives me a list of guests and gets me their sum they spent. Now I need to get only the guest who has the maximum sumcost instead of getting the whole list of all the guests. How can I accomplish this?

CodePudding user response：

SELECT
   s.ggid,
   s.sumcost
FROM (SELECT 
         g.guest_id ggid, 
         sum(room_price) sumcost
         FROM booking b
         INNER JOIN guest g ON b.guest_id = g.guest_id
         INNER JOIN room r ON r.room_id = b.room_id
         GROUP BY g.guest_id
         ORDER BY sumcost  DESC) s
WHERE s.sumcost = ( SELECT sum(room_price) sumcost
                    FROM booking b
                    INNER JOIN guest g ON b.guest_id = g.guest_id
                    INNER JOIN room r ON r.room_id = b.room_id
                    GROUP BY g.guest_id
                    ORDER BY sumcost DESC
                    LIMIT 1
                   )

CodePudding user response：

You can use a window function, e.g. MAX OVER:

SELECT g.guest_id, g.name, g.email, sums.sumcost
FROM guest g
JOIN
(
  SELECT
    b.guest_id, SUM(r.room_price) AS sumcost,
    MAX(SUM(r.room_price)) OVER () AS maxsumcost
  FROM booking b
  INNER JOIN room r ON r.room_id = b.room_id
  GROUP BY g.guest_id
) sums ON sums.guest_id = g.guest_id AND sums.sumcost = maxsumcost;