How to display multiple items but only the highest values in year and month for each?

Question

I have the following two tables:

CREATE DATABASE IF NOT EXISTS springbootdb;
DROP TABLE IF EXISTS occupancy;
DROP TABLE IF EXISTS hotel;

CREATE TABLE hotel
(
    id      INT  NOT NULL PRIMARY KEY auto_increment,
    category int NOT NULL,
    name    TEXT NOT NULL,
    owner   TEXT NOT NULL,
    contact TEXT NOT NULL,
    address TEXT NOT NULL,
    city    TEXT NOT NULL,
    zip     TEXT NOT NULL,
    phone   TEXT NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE occupancy
(
    id              int not null primary key auto_increment,
    hotelid         int not null,
    month           int not null,
    year            int not null,
    room_utilization int not null,
    bed_utilization  int not null,
    room_count       int not null,
    bed_count        int not null,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Now I want to display every single hotel.id and hotel.name along with occupancy.room_count, occupancy.bed_count, occupancy.room_utilization and occupancy.bed_utilization - but only the very latest entry for each hotel.id, so the ones where occupancy.year and occupancy.month are the highest values each.

I tried a couple of things, such as

SELECT springbootdb.hotel.id, springbootdb.hotel.name, springbootdb.occupancy.bed_count, springbootdb.occupancy.bed_utilization 
From springbootdb.hotel 
INNER JOIN springbootdb.occupancy 
ON hotel.id = occupancy.hotelid
order by springbootdb.occupancy.`year`, springbootdb.occupancy.`month` asc limit 1;

but haven't had any success unfortunately.

Can a good soul tell me how to get there? Thanks!

Answer 1

This is best solved with window functions, but that requires you upgrade to MySQL 8.0.

Here's the general idea.

SELECT t.id, t.name, t.bed_count, t.bed_utilization
FROM (
  SELECT h.id, h.name, o.bed_count, o.bed_utilization, ROW_NUMBER() OVER (PARTITION BY h.id ORDER BY o.`year` DESC, o.`month` DESC) AS rownum
  FROM hotel AS h JOIN occupacy AS o ON h.id = o.hotelid
) AS t
WHERE t.rownum = 1;