Join to another table only matching specific records

I have a table of ports:

drop table if exists ports;
create table ports(id int, name char(20));

insert into ports (id, name ) values
  (1, 'Port hedland'), 
  (2, 'Kwinana');

And a table of tariffs connected to those ports:

drop table if exists tariffs;
create table tariffs(id int, portId int, price decimal(12,2), expiry bigint(11));

insert into tariffs (id, portId, price, expiry ) values
    (1, 2, 11.00, 1648408400), 
    (2, 2, 12.00, 1648508400), 
    (3, 2, 13.00, 1648594800), 
    (4, 2, 14.00, 1651273200),
    (5, 2, 15.00, 2250000000 );

insert into tariffs (id, portId, price, expiry ) values
    (1, 1, 21.00, 1648408400), 
    (2, 1, 22.00, 1648508400), 
    (3, 1, 23.00, 1648594800), 
    (4, 1, 24.00, 1651273200),
    (5, 1, 25.00, 2250000000 );

Each tariff has an expiry.

I can easily make a query to figure out the right tariff for as specific date for each port. For example at timestamp 1648594700 the right tariff is:

SELECT * FROM tariffs 
WHERE 1648594700 < expiry AND portId = 2
ORDER BY expiry
LIMIT 1

Result:

id  portId  price   expiry
3   2   13.00   1648594800

However, in my application I want to be able to pull in the right tariff starting from the ports record.

For one record, I can do this:

SELECT * FROM ports 
LEFT JOIN tariffs on tariffs.portId = ports.id 
WHERE 1648594700 < tariffs.expiry AND ports.id = 2
LIMIT 1

Result:

id  name    id  portId  price   expiry
2   Kwinana 3   2   13.00   1648594800

This feels a little 'dirty', especially because I am doing a lookup on a record, and then forcing only one result using LIMIT. But, OK.

What I cannot do, and can't work out how to do, is a query that will return a list of ports, and each port having a price field that matches the constraint above (that is, the record with the highest expiry compared to 1648594700 for each port).

This obviously won't work:

SELECT * FROM ports 
left join tariffs on tariffs.portId = ports.id
where 1648594700 < tariffs.expiry

Since the result of the query, testing with timestamp 1648594700, would be:

id  name    id  portId  price   expiry
2   Kwinana 3   2   13.00   1648594800
2   Kwinana 4   2   14.00   1651273200
2   Kwinana 5   2   15.00   2250000000
1   Port he 3   1   23.00   1648594800
1   Port he 4   1   24.00   1651273200
1   Port he 5   1   25.00   2250000000

Instead, the result for all ports (before further filtering) should be:

id  name    id  portId  price   expiry
2   Kwinana 3   2   13.00   1648594800
1   Port he 3   1   23.00   1648594800

Is there a clean, non-hacky way to have such a result? As an added constraint, is this possible for this to be done in ONE query, without temp tables etc.?

2 answers

  • answered 2022-05-07 04:44 Tim Biegeleisen

    On MySQL 8+, ROW_NUMBER should work here:

    WITH cte AS (
        SELECT p.id, p.name, t.price, t.expiry,
               ROW_NUMBER() OVER (PARTITION BY p.id ORDER BY t.expiry) rn
        FROM ports p
        LEFT JOIN tariffs t ON t.portId = p.id
        WHERE t.expiry > 1648594700
    )
    
    SELECT id, name, price, expiry
    FROM cte
    WHERE rn = 1
    ORDER BY id;
    

    This logic would return one record for each port having the nearest expiry.

  • answered 2022-05-07 04:47 Jonas Metzler

    You can select the lowest expiry, do your join and only take the rows having this minimum expiry:

    SELECT p.id, p.name, t.id, t.portId, t.price, t.expiry
    FROM ports p 
    LEFT JOIN tariffs t ON p.id = t.portId
    WHERE expiry = (SELECT MIN(expiry) FROM tariffs WHERE 1648594700 < expiry)
    ORDER BY p.id;
    

    This will get your desired result, please see here: db<>fiddle

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum