Find all results by a relationships max column value

I'm not terribly good with complex database queries and this melted my brain.

I want to get Swimmers by specific certificate ID, but the certificate ID match should be determined by the order column.

Swimmers have multiple certificates and certificates can be added between existing certificates. Say the system starts off with Level 1 and Level 2. Eventually we have level 1.1, 1.2, and 1.3 so we have an order column to determine the next and previous certificate that should be earned.

The relationship looks like this:

Swimmer ( id ) -> SwimmerCertificate ( swimmer_id, certificate_id ) -> Certificate ( id, order, name )

Swimmer One = Swimmer->certificates = [ certificate_id = 1,2,3,4 ]
Swimmer Two = Swimmer->certificates = [ certificate_id = 1,2,4 ]

Certificates = [
    1 => [ ‘order’ => 1],
    2 => [ ‘order’ => 2],
    3 => [ ‘order’ => 4], // Higher order
    4 => [ ‘order’ => 3]
];

The swimmers maximum certificate is determined by max order.

So if I wanted swimmers by certificate ID 4 in this case I would not get this swimmer One I would get swimmer Two

If I wanted swimmers by certificate ID 3 then I would get Swimmer One

I'd love to know how this sound be approached.

Ultimately, I opted to add a current certificate to the Swimmers table maintained by a model observer.