CODEWITHSUNDEEP
mysqlsqlmax
MatthewExpungement
MatthewExpungement

Reputation: 93

Compare Highest Values Between Two MySQL Tables

I have two tables, search_history and parse_history, that contain multiple rows of the same case number with different timestamps of when I last scraped it and in the other table when I last parsed it. I'm trying to write a query that will compare the highest timestamp value for a case number in the search_history table with the highest timestamp value for that case number in the parse_history table. If the timestamp for the most recent entry for that case number in search_history is higher than the most recent entry for that case number in the parse_history table then it is returned.

All the examples I've seen so far are for getting the highest values from multiple entries out of a single table using Max or groupwise max. I can't find anything on comparing the two.

Below are my two tables and in the example below I would need case number 4W90B2F to be returned since the most recent search_history entry has a higher timestamp than the most recent parse_history entry for that case number.

search_history Table
ID  CaseNumber  TimeStamp
1   4W90B2F 2017-09-30 00:25:33
2   0DB0NGV 2017-09-30 00:15:35
3   4W90B2F 2017-10-05 00:15:44
4   0DB0NGV 2017-10-10 00:53:13
5   4W90B2F 2017-10-20 00:25:34

parse_history Table
ID  CaseNumber  TimeStamp
1   4W90B2F 2017-10-01 00:25:33
2   0DB0NGV 2017-10-02 00:15:35
3   4W90B2F 2017-10-06 00:15:44
4   0DB0NGV 2017-10-11 00:53:13

SQL Fiddle Link to sample http://sqlfiddle.com/#!9/bc229f

My attempt so far times out

SELECT sh.*
FROM search_history sh
LEFT JOIN search_history b
ON sh.CaseNumber = b.CaseNumber AND sh.Timestamp < b.Timestamp
INNER JOIN
parse_history as ph
LEFT JOIN parse_history c
ON ph.CaseNumber = c.CaseNumber AND ph.Timestamp < c.Timestamp
WHERE b.CaseNumber IS NULL AND
c.CaseNumber IS NULL
LIMIT 50

Upvotes: 0

Views: 57

Answers (2)

Strawberry
Strawberry

Reputation: 33935

SELECT x.*
  FROM search_history x
  LEFT 
  JOIN parse_history y
    ON y.casenumber = x.casenumber 
   AND y.Timestamp > x.Timestamp
 WHERE y.id IS NULL;

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

You can select from queries. So select the maximum timestamp per casenumber from both tables and compare.

select 
from (select casenumber, max(timestamp) as maxt from search_history group by casenumber) sh
join (select casenumber, max(timestamp) as maxt from parse_history group by casenumber) ph
  on sh.casenumber = ph.casenumber and sh.maxt > ph.maxt;

Upvotes: 1

Related Questions