HMGqWhSQ

1-- sqlite
2
3create table if not exists records (
4    id INTEGER PRIMARY KEY,
5    queried_at INTEGER,
6    datetime INTEGER,
7    domain TEXT,
8    type TEXT,
9    answers TEXT
10);
11
12insert into records values
13    (1, 0, 5, 'dom1', 'type1', 'ans1, ans1, ans1'),
14    (2, 335, 14, 'dom1', 'type1', 'safvasdfin'),
15    (3, 5, 24, 'dom2', 'type2', 'asfasgfasgfasd'),
16    (4, 335, 336, 'dom2', 'type2', 'asdgasgasgs'),
17    (5, 335, 12, 'dom1', 'type1', 'gsdgsrgaseg'),
18    (6, 235, 15, 'dom3', 'type1', 'gasdgadga'),
19    (7, 354, 35, 'dom4', 'type2', 'asgasdgasg'),
20    (8, 335, 235, 'dom4', 'type2', 'asdgasgasg'),
21    (9, 235, 15, 'dom3', 'type1', 'sdgsdg'),
22    (10, 354, 35, 'dom4', 'type2', 'asdg'),
23    (11, 335, 235, 'dom4', 'type2', 'asdgwettwgt'),
24    (12, 354, 35, 'dom4', 'type2', 'egwsrdg<'),
25    (13, 335, 235, 'dom4', 'type2', 'shggh<srgbrsdg'),
26    (14, 235, 15, 'dom3', 'type1', 'edgheghsawgdsawg'),
27    (15, 354, 35, 'dom4', 'type2', 'asdg'),
28    (16, 335, 235, 'dom4', 'type2', 'wqaefg');
29
30
31DROP TABLE IF EXISTS temp.a;
32DROP TABLE IF EXISTS temp.b; 
33
34CREATE TEMP TABLE temp.b AS SELECT queried_at, datetime, domain, type, answers
35FROM records
36WHERE queried_at = ( SELECT MAX(queried_at) FROM records );
37
38CREATE TEMP TABLE temp.a AS SELECT queried_at, datetime, domain, type, answers
39FROM records
40WHERE queried_at = ( 
41    SELECT MAX(queried_at)
42    FROM records
43    WHERE queried_at NOT IN ( SELECT MAX(queried_at) FROM records ) );
44
45SELECT a.queried_at, b.queried_at, a.domain, a.type, a.datetime, a.answers, b.datetime, b.answers FROM a INNER JOIN b ON a.domain = b.domain AND a.type = b.type WHERE a.answers <> b.answers;