Search code examples
mysqlsqlselectmaxdate

SQL Select latest result from table where keyword = and competitor =

I have two tables as such:

tablename: input_keywords
Cols: keyword, last_check, pages_deep, check_freq_days

tablename: rank_result
Cols: keyword, result_url, position, last_check, competitor

input_keywords.keyword is unique, so that we don't look for keywords more than one.

rank_result keeps all data from our crawls and stores some metadata.

I need to show the following

a = input_keywords
b = rank_result

a.keyword, a.last_check, b.position WHERE b.competitor = 'xxx'

but only select the LAST / Most recent result from rank_result

I have attempted to follow several other answers but not getting the results I would expect.

Update1

CREATE TABLE `input_keywords` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `keyword` char(150) DEFAULT NULL COMMENT 'the keyword....',
  `last_check` timestamp NULL DEFAULT '2000-01-01 00:00:00' COMMENT 'Last check timestamp, default to years ago so we check immediatly',
  `CREATION` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `MODIFICATION` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `p_deep` int(1) DEFAULT '5' COMMENT 'how many pages deep to search - default 5',
  `check_freq_days` int(11) DEFAULT '3' COMMENT 'how often to check this keyword in DAYS default 3',
  `type` char(50) DEFAULT NULL COMMENT 'Product, Category, other etc',
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNQ_Keyword` (`keyword`)
) ENGINE=InnoDB AUTO_INCREMENT=5865 DEFAULT CHARSET=utf8;


CREATE TABLE `rank_result` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `keyword` char(150) DEFAULT '',
  `result_url` text,
  `position` int(11) DEFAULT NULL,
  `check_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `useragent_used` char(255) DEFAULT NULL,
  `proxy_log` text,
  `check_date` date DEFAULT NULL COMMENT 'date of the check - easier for graph plotting',
  `competitor` tinytext,
  PRIMARY KEY (`id`),
  KEY `idx_KEYWORD` (`keyword`),
  KEY `keyword` (`keyword`,`check_time`,`competitor`(50))
) ENGINE=InnoDB AUTO_INCREMENT=2868936 DEFAULT CHARSET=utf8;

Example Contents of input_keywords

----------------------------------------------------------------------------------------------------------------------------------
| id | keyword               | last_check          | CREATION            | MODIFICATION        | p_deep | check_freq_days | type |
----------------------------------------------------------------------------------------------------------------------------------
| 1  | acoustic guitars      | 2017-03-07 17:03:55 | 2017-01-20 12:27:17 | 2017-03-07 17:03:55 | 5      | 1               | NULL |
----------------------------------------------------------------------------------------------------------------------------------
| 2  | guitar accessories    | 2017-03-05 11:03:49 | 2017-01-20 12:27:27 | 2017-03-05 11:03:49 | 5      | 3               | NULL |
----------------------------------------------------------------------------------------------------------------------------------
| 3  | guitar amps           | 2017-03-05 11:04:05 | 2017-01-20 12:27:33 | 2017-03-05 11:04:06 | 5      | 3               | NULL |
----------------------------------------------------------------------------------------------------------------------------------
| 4  | guitar strings        | 2017-03-05 13:03:51 | 2017-01-20 12:27:42 | 2017-03-05 13:03:51 | 5      | 3               | NULL |
----------------------------------------------------------------------------------------------------------------------------------
| 5  | guitar effects pedals | 2017-03-05 11:03:43 | 2017-01-20 12:27:50 | 2017-03-05 11:03:43 | 5      | 3               | NULL |
----------------------------------------------------------------------------------------------------------------------------------



---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| id  | keyword          | result_url                           | position | check_time          | useragent_used                       | proxy_log | check_date | competitor   |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 493 | acoustic guitars | http://www.competitor.com/… | 1        | 2017-01-18 09:36:17 | Mozilla/5.0 (compatible; MSIE 10.0;… | NULL      | 2017-01-18 | Competitor1      |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 494 | acoustic guitars | http://competitor2.com… | 2        | 2017-01-18 09:36:17 | Mozilla/5.0 (compatible; MSIE 10.0;… | NULL      | 2017-01-18 | Competitor2   |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 495 | acoustic guitars | https://out_website.com | 3        | 2017-01-18 09:36:17 | Mozilla/5.0 (compatible; MSIE 10.0;… | NULL      | 2017-01-18 | US    |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 496 | acoustic guitars | http://competitor3.com | 4        | 2017-01-18 09:36:17 | Mozilla/5.0 (compatible; MSIE 10.0;… | NULL      | 2017-01-18 | Competitor3 |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 497 | acoustic guitars | http://competitor4.com | 5        | 2017-01-18 09:36:17 | Mozilla/5.0 (compatible; MSIE 10.0;… | NULL      | 2017-01-18 | NULL         |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 498 | acoustic guitars | https://www.amazon.co.uk/acoustic-g… | 6        | 2017-01-18 09:36:17 | Mozilla/5.0 (compatible; MSIE 10.0;… | NULL      | 2017-01-18 | Amazon       |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 499 | acoustic guitars | http://compx.com | 7        | 2017-01-18 09:36:17 | Mozilla/5.0 (compatible; MSIE 10.0;… | NULL      | 2017-01-18 | compX          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 500 | acoustic guitars | http://compx.com | 8        | 2017-01-18 09:36:17 | Mozilla/5.0 (compatible; MSIE 10.0;… | NULL      | 2017-01-18 | compX      |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 501 | acoustic guitars | http://www. compx.com/  | 9        | 2017-01-18 09:36:17 | Mozilla/5.0 (compatible; MSIE 10.0;… | NULL      | 2017-01-18 | NULL         |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 502 | acoustic guitars | http://www. compx.com… | 10       | 2017-01-18 09:36:17 | Mozilla/5.0 (compatible; MSIE 10.0;… | NULL      | 2017-01-18 | NULL         |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 503 | acoustic guitars | http://www. compx.com/        | 11       | 2017-01-18 09:36:17 | Mozilla/5.0 (compatible; MSIE 10.0;… | NULL      | 2017-01-18 | NULL         |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 504 | acoustic guitars | http://www. compx.com… | 12       | 2017-01-18 09:36:17 | Mozilla/5.0 (compatible; MSIE 10.0;… | NULL      | 2017-01-18 | NULL         |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

This table has a record for each day we have checked the keyword ranking in (so there are lots of record for the same keyword and competitor)

I only require our rankings IF we have one, else return just the keyword.

My Ideal output is 

|keyword  | last_check | p_deep | check_freq_days | position |
|keyword1 | 2017-03-06 | 5      |.  3.            | 4.       |
|keyword2 | 2017-03-06 | 5.     |.  3.            | NULL.    |

My best attempt is:

select input_keywords.keyword, input_keywords.last_check, input_keywords.p_deep, `input_keywords`.`check_freq_days`, rank_result.position from input_keywords join rank_result on input_keywords.keyword=rank_result.keyword where rank_result.competitor = 'OurCompany' and input_keywords.last_check=rank_result.check_time

And this NEARLY works but does not return a result if we are not listed.

As I'm starting from the input_keywords table I was hoping to list all of the keywords, and our current rank, if any.

Solution

  • I've worked this out by doing the following.

    1. Add a competitor field to the input_keywords table
    2. Added some indexes to make the query speedy

    Created the following SQL

    SELECT i.`keyword`, i.`last_check`, i.`p_deep`, i.`check_freq_days`, r.`position`
  FROM input_keywords i
  LEFT JOIN rank_result r 
    ON i.`keyword` = r.`keyword` AND i.`competitor` = r.`competitor` AND i.`last_check` = r.`check_time`

    This then returns me a single row per input keyword, with our last known position in the rankings