Return all the second highest valued rows SQL

user_17606103 0
Viewed 64

Let's say I have a table called bookings, containing 3 columns: hotelid, timestampOfBookingand numberOfGuests.

How do I return all the dates on which the second highest beds were booked (the amount of beds booked is the same as the number of guests).

In other words, I'm looking for the dates on which the second maximum number of numberOfGuestsoccur. This means that in the event of a tie (where there is more than 1 date on which the described condition applies), it should return all those dates. In the event that all the dates have exactly the same numberOfGuests the query should return nothing.

If possible, I would only like to have one column in the query result that contains those specific dates.

Example:

hotelid      timestampOfBooking      numberOfGuests 
  11             22/11/2021                 2
  34             23/11/2021                 2
  30             23/11/2021                 5
  19             24/11/2021                 7
  8              25/11/2021                 12
  34             25/11/2021                 5

In this case two dates should be in the result: 23/11/2021 and 24/11/2021 as they both had 7 numberOfGuests. The max numberOfGuests here is 17 (occurs on 25/11/2021) and 7 is the second highest, explaining why 23/11/2021 (2 + 5) and 24/11/2021 (7) are returned. The final result should look like this:

dates
23/11/2021
24/11/2021
postgresql sql
1 Answers

Method 1:

You can use DENSE_RANK() with SUM(numberOfGuests) IN DESC:

SELECT timestampOfBooking, total_beds FROM
(
   select timestampOfBooking, 
          sum(numberOfGuests) as total_beds,
          dense_rank() over (order by sum(numberOfGuests) DESC) as rnk
   from bookings
   group by timestampOfBooking
) as sq
where rnk = 2

Method 2:

Using OFFSET and LIMIT:

SELECT timestampOfBooking, 
       SUM(numberOfGuests) AS total_beds
FROM bookings
GROUP BY timestampOfBooking
HAVING sum(numberOfGuests)=
   (
    SELECT distinct SUM(numberOfGuests) AS total_beds
    FROM bookings
    GROUP BY timestampOfBooking
    ORDER BY total_beds DESC
    OFFSET 1 LIMIT 1
   );

Both the methods will give you same output.

Working fiddle

user_170156930
Related