MYSQL - Group by and select highest result and use JOIN?

Question

I have 2 SQL tables, I need to pull the highest timestamp from the 2nd table and that timestamp needs to be within a range.

Table: Projects 
ID,Name,invoiceTotal,department
1,Project1,100,1
2,Project2,200,3
3,Project3,300,2
4,Project4,100,2
5,Project5,400,1


Table: Manning
ProjectID,timestamp
1,1547038287
1,1515558287
1,1501118287
5,1471118287
5,1481118287

I have been playing about with using PHP to firstly run a query that has an inner join so i can pull both tables data.

SELECT
project.ID,
project.name,
project.invoiceTotal,
manning.timestamp
FROM
manning
INNER JOIN projects ON project.ID = manning.ProjectID
WHERE
project.department = 1 AND
manning.timestamp BETWEEN 1547038287 AND 1301118287
ORDER BY
manning.timestamp ASC

I'm then looping through with PHP and running procedural code to sort the data and then display it. The problem is it's slow, and it's definitely not the most efficient way of doing it.

So with this example data i would like to have the result:

1,Project1,100,1547038287
5,Project5,400,1481118287

I've been trying to use the "group by" however if the order of the manning table is not right i'm not getting the right results.

Thank you for any help. I've tried to use sqlfiddle.com however it seems to be down for me right now :(

Possible duplicate of [Get records with max value for each group of grouped SQL results](https://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results) — sticky bit, Jan 09 '19 at 13:15
`manning.timestamp BETWEEN 1547038287 AND 1301118287` is short for `manning.timestamp >= 1547038287 AND manning.timestamp <= 1301118287` which is never true, because 1547038287 is greater than 1301118287. — Thorsten Kettner, Jan 09 '19 at 13:16
@ThorstenKettner noted, an oversight on my part while bashing out the example query. — JimmyUK1, Jan 09 '19 at 13:20

Thorsten Kettner · Accepted Answer · 2019-01-09T13:33:40.437

0

Don't join all manning rows, but only the latest one per project:

select
  p.ID,
  p.name,
  p.invoiceTotal,
  m.max_timestamp
from projects p
join
(
  select projectid, max(timestamp) as max_timestamp
  from manning
  where timestamp between 1301118287 and 1547038287
  group by projectid
) m on m.projectid = p.id
where p.department = 1;

This is not only a clean way to write this query, but also a very performant approach. Why join all records, when your are only interested in very few of them? Don't produce a huge intermediate result you must get rid of again.

edited Jan 09 '19 at 13:33

answered Jan 09 '19 at 13:22

Thorsten Kettner

89,309
7
49
73

Thanks to all that replied and put their time into this. It's working and much faster than it was before :) – JimmyUK1 Jan 09 '19 at 13:38

score 0 · Answer 2 · answered Jan 09 '19 at 13:22

Try :

SELECT
    p.ID,
    p.name,
    p.invoiceTotal,
    maxm.ts
FROM
    project p
    INNER JOIN (
        SELECT m.projectID, max(m.timestamp) ts
        FROM manning m
        WHERE p.ID = m.ProjectID AND m.timestamp BETWEEN 1301118287 AND 1547038287
        GROUP BY m.projectID
    ) maxm ON maxm.projectID = p.id
ORDER BY m.timestamp ASC

Tim Biegeleisen · Answer 3 · 2019-01-09T13:29:34.460

If you're using MySQL 8+ or later, then a nice way to handle this is to use ROW_NUMBER():

WITH cte AS (
    SELECT p.ID, p.Name, p.invoiceTotal, m.timestamp,
        ROW_NUMBER() OVER (PARTITION BY m.ProjectID ORDER BY m.timestamp DESC) rn
    FROM Projects p
    INNER JOIN Manning m
        ON p.ID = m.ProjectID
    WHERE m.timestamp BETWEEN 1301118287 and 1547038287
)

SELECT ID, Name, invoiceTotal, timestamp
FROM cte
WHERE rn = 1;

Demo

If you are using a version of MySQL earlier than 8, and you want the ability to reduce the amount of code you need to write for many types of queries, then consider upgrading. Check the other answers for how to approach your problem on earlier versions of MySQL.

MYSQL - Group by and select highest result and use JOIN?

3 Answers3

Demo