Return all the second highest valued rows SQL-CodePudding

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

CodePudding user response：

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

Working fiddle

CodePudding user response：

It can be done via dense_rank (not rank). You also need a group by but the aggregates can be used inside window functions:

with cte as (
   select timestampofbooking, sum(numberofguests) as sumofbeds, dense_rank() over (order by sum(numberofguests) desc) as dr
   from bookings
   group by timestampofbooking
)
select *
from cte
where dr = 2

Or just use two group by:

select timestampofbooking, sum(numberofguests) as sumofbeds
from bookings
group by timestampofbooking
having sum(numberofguests) = (
    select distinct sum(numberofguests)
    from bookings
    group by timestampofbooking
    order by sum(numberofguests)
    offset 1 limit 1
)