How can I filter my table so that it shows the newest data possible?-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

If you just want a list of rooms, I suggest using aggregation:

SELECT roomNo
FROM booking
GROUP BY roomNo
HAVING SUM(YEAR(dateTo) >= YEAR(CURRENT_DATE) - 2) = 0;

If you want all columns, then use exists logic:

SELECT b1.*
FROM booking b1
WHERE NOT EXISTS (
    SELECT 1
    FROM booking b2
    WHERE b2.roomNo = b1.roomNo AND
          YEAR(b2.dateTo) >= YEAR(CURRENT_DATE) - 2
);

CodePudding user response：

You might want to try the below

SELECT roomNo, dateto FROM booking where dateto =(SELECT MAX(dateto) FROM booking)

you can then order by roomNo or dateto.