I have a table with hotel check-in dates and prices with timestamps. I'm trying to figure out how to write a query that will select the most recently added item per checkin date, for each hotel, as long as it is in the last six hours.
Example table -
| Property | CheckIn | Price | timestamp |
|---|---|---|---|
| HotelA | 2022-03-27 | $69.99 | 2022-03-27 05:44:18 |
| HotelB | 2022-03-27 | $79.99 | 2022-03-27 05:44:11 |
| HotelC | 2022-03-27 | $75.99 | 2022-03-27 05:44:15 |
| HotelA | 2022-03-28 | $65.99 | 2022-03-27 05:44:18 |
| HotelB | 2022-03-28 | $77.99 | 2022-03-27 05:44:21 |
| HotelC | 2022-03-28 | $79.99 | 2022-03-27 05:44:25 |
| HotelA | 2022-03-29 | $63.99 | 2022-03-27 05:44:27 |
| HotelB | 2022-03-29 | $75.99 | 2022-03-27 05:44:31 |
| HotelC | 2022-03-29 | $77.99 | 2022-03-27 05:44:33 |
| HotelA | 2022-03-27 | $35.99 | 2022-03-27 05:54:05 |
| HotelB | 2022-03-27 | $40.99 | 2022-03-27 05:54:08 |
| HotelC | 2022-03-27 | $45.99 | 2022-03-27 05:54:11 |
| HotelA | 2022-03-28 | $47.99 | 2022-03-27 05:54:15 |
| HotelB | 2022-03-28 | $49.99 | 2022-03-27 05:54:18 |
| HotelC | 2022-03-28 | $51.99 | 2022-03-27 05:54:21 |
As you can see in the example table, there are multiple entries for HotelA for example, with a CheckIn on 2022-03-27. I just want the most recent one ($35.99).
I have tried using GROUP BY, but MySQL keeps telling me that I'm violating "only_full_group_by" mode unless I group by CheckIn, Price, and timestamp, which doesn't work, it displays all the values in the example table above.
To return results from the last 6 hours I'm using "WHERE timestamp \>= DATE_SUB(NOW(),INTERVAL 6 HOUR)" in the select statement, which does seem to work!
DISTINCT doesn't seem to work because each row is distinct.
Not sure what to try next.
Here's what I would like the statement to return based on the example table above -
| Property | CheckIn | Price | timestamp |
|---|---|---|---|
| HotelA | 2022-03-29 | $63.99 | 2022-03-27 05:44:27 |
| HotelB | 2022-03-29 | $75.99 | 2022-03-27 05:44:31 |
| HotelC | 2022-03-29 | $77.99 | 2022-03-27 05:44:33 |
| HotelA | 2022-03-27 | $35.99 | 2022-03-27 05:54:05 |
| HotelB | 2022-03-27 | $40.99 | 2022-03-27 05:54:08 |
| HotelC | 2022-03-27 | $45.99 | 2022-03-27 05:54:11 |
| HotelA | 2022-03-28 | $47.99 | 2022-03-27 05:54:15 |
| HotelB | 2022-03-28 | $49.99 | 2022-03-27 05:54:18 |
| HotelC | 2022-03-28 | $51.99 | 2022-03-27 05:54:21 |
If the timestamp column is missing from the output results that's fine too, as long as the rest of the data the query outputs is grouped properly. I'm most interested in the Property, CheckIn, and Price column.
The suggested answer is not working. They are manually entering the group names (which are actually numbers). There are hundreds of properties, which would take hours to manually list. Additionally the list would have to be maintained, which would take extra time.
