[FIXED] How can I filter my table so that it shows the newest data possible?

Issue

I have the following php/mysql database table called 'booking':

hotelNo | guestNo | dateFrom | dateTo | roomNo
   1         1     2022-03-10 2022-03-17  1   
   1         2     2019-07-01 2019-07-08  1
   4         3     2022-03-01 2022-03-09  6
   4         5     2022-07-15 2022-07-25  7
   5         6     2022-03-20 2022-03-29  10
   5         7     1987-03-10 1987-03-21  12

My task is to select all rooms which last had a booking that ended over two years ago. This is the query I wrote:

SELECT roomNo, dateTo FROM booking
WHERE Year(CURRENT_DATE)-2 > year(dateTo);

And the result is:

roomNo | dateTo
  1    2019-07-08
  12   1987-03-21

However, this is not correct. room 1 gets booked at 2022-03-10, therefore it shouldn't appear on the result. What should I implement in my query so that it filters out the older bookings first and then checks if the newest booking for that room ended over 2 years ago?

So far I tried using DISTINCT and ORDER BY clauses in my query but it didn't work for me.

Solution

You can group by roomNo and put your condition in the having clause:

select roomNo, max(dateTo) as dateTo
from booking
group by roomNo
having Year(CURRENT_DATE) - 2 > year(max(dateTo));

Fiddle

Answered By - Zakaria

Answer Checked By - Marilyn (PHPFixing Volunteer)