MYSQL - Compare rows in same table and show distinct
-1
I want to compare rows in the same table and show what has changed from last days data
For Eg: if my Metrics table is as below
id | Server | Component | Status | Date
------+----------+-------------------------------------------
1 | Node1 | Service | Compliant | 10-1-2018
2 | Node1 | IP | Compliant | 10-1-2018
3 | Node2 | Service | Non-Compliant | 10-1-2018
4 | Node2 | IP | Compliant | 10-1-2018
5 | Node1 | Service | Compliant | 09-1-2018
6 | Node1 | IP | Compliant | 09-1-2018
7 | Node2 | Service | Compliant | 09-1-2018
8 | Node2 | IP | Compliant | 09-1-2018
The result I want is as below because the Node 2 service compliance state has changed (to Non-Compliant) compared with the last day state
Node2 | Service | Non-Compliant | 09-1-2018
What I want is basically to track compliance status change for a node when compared with last day data
Thanks
mysql
asked Nov 23 '18 at 17:52
BharathBharath
103
|
show 6 more comments
-1
I want to compare rows in the same table and show what has changed from last days data
For Eg: if my Metrics table is as below
id | Server | Component | Status | Date
------+----------+-------------------------------------------
1 | Node1 | Service | Compliant | 10-1-2018
2 | Node1 | IP | Compliant | 10-1-2018
3 | Node2 | Service | Non-Compliant | 10-1-2018
4 | Node2 | IP | Compliant | 10-1-2018
5 | Node1 | Service | Compliant | 09-1-2018
6 | Node1 | IP | Compliant | 09-1-2018
7 | Node2 | Service | Compliant | 09-1-2018
8 | Node2 | IP | Compliant | 09-1-2018
The result I want is as below because the Node 2 service compliance state has changed (to Non-Compliant) compared with the last day state
Node2 | Service | Non-Compliant | 09-1-2018
What I want is basically to track compliance status change for a node when compared with last day data
Thanks
mysql
asked Nov 23 '18 at 17:52
BharathBharath
103
Are you using MySQL/MariaDB or SQL Server (TSQL) ? Please dont spam tag all the RDBMS
– Madhur Bhaiya
Nov 23 '18 at 17:53
I am using Mysql, i am new to databases and learning. I thought SQL logic is more or less same with all RDBMS and thought someone could help me learn. Thanks
– Bharath
Nov 23 '18 at 18:00
You have multiple entries with different Status values on same date. This is conflicting with your defined logic.
– Madhur Bhaiya
Nov 23 '18 at 18:00
Each RDBMS uses its own version of SQL flavour. So it is best to specify the exact RDBMS you are using.
– Madhur Bhaiya
Nov 23 '18 at 18:01
Ok, i have edited to reflect MySQL. Thanks
– Bharath
Nov 23 '18 at 18:02
|
show 6 more comments
-1
-1
-1
I want to compare rows in the same table and show what has changed from last days data
For Eg: if my Metrics table is as below
id | Server | Component | Status | Date
------+----------+-------------------------------------------
1 | Node1 | Service | Compliant | 10-1-2018
2 | Node1 | IP | Compliant | 10-1-2018
3 | Node2 | Service | Non-Compliant | 10-1-2018
4 | Node2 | IP | Compliant | 10-1-2018
5 | Node1 | Service | Compliant | 09-1-2018
6 | Node1 | IP | Compliant | 09-1-2018
7 | Node2 | Service | Compliant | 09-1-2018
8 | Node2 | IP | Compliant | 09-1-2018
The result I want is as below because the Node 2 service compliance state has changed (to Non-Compliant) compared with the last day state
Node2 | Service | Non-Compliant | 09-1-2018
What I want is basically to track compliance status change for a node when compared with last day data
Thanks
mysql
asked Nov 23 '18 at 17:52
BharathBharath
103
I want to compare rows in the same table and show what has changed from last days data
For Eg: if my Metrics table is as below
id | Server | Component | Status | Date
------+----------+-------------------------------------------
1 | Node1 | Service | Compliant | 10-1-2018
2 | Node1 | IP | Compliant | 10-1-2018
3 | Node2 | Service | Non-Compliant | 10-1-2018
4 | Node2 | IP | Compliant | 10-1-2018
5 | Node1 | Service | Compliant | 09-1-2018
6 | Node1 | IP | Compliant | 09-1-2018
7 | Node2 | Service | Compliant | 09-1-2018
8 | Node2 | IP | Compliant | 09-1-2018
The result I want is as below because the Node 2 service compliance state has changed (to Non-Compliant) compared with the last day state
Node2 | Service | Non-Compliant | 09-1-2018
What I want is basically to track compliance status change for a node when compared with last day data
Thanks
mysql
mysql
asked Nov 23 '18 at 17:52
BharathBharath
103
asked Nov 23 '18 at 17:52
BharathBharath
103
edited Nov 23 '18 at 18:18
Bharath
asked Nov 23 '18 at 17:52
BharathBharath
103
asked Nov 23 '18 at 17:52
BharathBharath
103
asked Nov 23 '18 at 17:52
BharathBharath
103
103
Are you using MySQL/MariaDB or SQL Server (TSQL) ? Please dont spam tag all the RDBMS
– Madhur Bhaiya
Nov 23 '18 at 17:53
I am using Mysql, i am new to databases and learning. I thought SQL logic is more or less same with all RDBMS and thought someone could help me learn. Thanks
– Bharath
Nov 23 '18 at 18:00
You have multiple entries with different Status values on same date. This is conflicting with your defined logic.
– Madhur Bhaiya
Nov 23 '18 at 18:00
Each RDBMS uses its own version of SQL flavour. So it is best to specify the exact RDBMS you are using.
– Madhur Bhaiya
Nov 23 '18 at 18:01
Ok, i have edited to reflect MySQL. Thanks
– Bharath
Nov 23 '18 at 18:02
|
show 6 more comments
Are you using MySQL/MariaDB or SQL Server (TSQL) ? Please dont spam tag all the RDBMS
– Madhur Bhaiya
Nov 23 '18 at 17:53
I am using Mysql, i am new to databases and learning. I thought SQL logic is more or less same with all RDBMS and thought someone could help me learn. Thanks
– Bharath
Nov 23 '18 at 18:00
You have multiple entries with different Status values on same date. This is conflicting with your defined logic.
– Madhur Bhaiya
Nov 23 '18 at 18:00
Each RDBMS uses its own version of SQL flavour. So it is best to specify the exact RDBMS you are using.
– Madhur Bhaiya
Nov 23 '18 at 18:01
Ok, i have edited to reflect MySQL. Thanks
– Bharath
Nov 23 '18 at 18:02
Are you using MySQL/MariaDB or SQL Server (TSQL) ? Please dont spam tag all the RDBMS
– Madhur Bhaiya
Nov 23 '18 at 17:53
Are you using MySQL/MariaDB or SQL Server (TSQL) ? Please dont spam tag all the RDBMS
– Madhur Bhaiya
Nov 23 '18 at 17:53
I am using Mysql, i am new to databases and learning. I thought SQL logic is more or less same with all RDBMS and thought someone could help me learn. Thanks
– Bharath
Nov 23 '18 at 18:00
I am using Mysql, i am new to databases and learning. I thought SQL logic is more or less same with all RDBMS and thought someone could help me learn. Thanks
– Bharath
Nov 23 '18 at 18:00
You have multiple entries with different Status values on same date. This is conflicting with your defined logic.
– Madhur Bhaiya
Nov 23 '18 at 18:00
You have multiple entries with different Status values on same date. This is conflicting with your defined logic.
– Madhur Bhaiya
Nov 23 '18 at 18:00
Each RDBMS uses its own version of SQL flavour. So it is best to specify the exact RDBMS you are using.
– Madhur Bhaiya
Nov 23 '18 at 18:01
Each RDBMS uses its own version of SQL flavour. So it is best to specify the exact RDBMS you are using.
– Madhur Bhaiya
Nov 23 '18 at 18:01
Ok, i have edited to reflect MySQL. Thanks
– Bharath
Nov 23 '18 at 18:02
Ok, i have edited to reflect MySQL. Thanks
– Bharath
Nov 23 '18 at 18:02
|
show 6 more comments
1 Answer
1
active
oldest
votes
0
http://sqlfiddle.com/#!9/1b0c46/1
SELECT t_next.*
FROM t1 t_prev
JOIN t1 t_next
ON t_next.server = t_prev.server
AND t_next.component = t_prev.component
AND t_next.date = t_prev.date + INTERVAL 1 DAY
AND t_next.status != t_prev.status
WHERE t_prev.date = '2018-01-09'
answered Nov 23 '18 at 18:35
AlexAlex
14.4k11937
Thanks Alex. That worked perfectly. Actually my data is more complex than the example i have given. I have learned from your approach and modified to my need as belowSELECT t_next.Server,t_next.Compliance-StatusFROM cd t_prev JOIN cd t_next ON t_next.Server= t_prev.ServerAND t_next.System-Parameter= t_prev.System-ParameterAND t_next.Compliance-Status!= t_prev.Compliance-StatusAND date(t_next.TOI) = date(t_prev.TOI) + INTERVAL 7 day WHERE t_next.Compliance-Status= 'Non-Compliant'
– Bharath
Nov 23 '18 at 19:31
Thanks, Alex. That worked perfectly. Actually, my data is more complex than the example I have given. I learned from your approach and modified as below to match my requirement.
– Bharath
Nov 23 '18 at 19:37
SELECT t_next.Server,t_next.Status,t_next.'DOR' FROM Table t_prev JOIN Table t_next ON t_next.Server= t_prev.ServerAND t_next.System-Parameter= t_prev.System-ParameterAND t_next.Status!= t_prev.StatusAND date(t_next.TOI) = date(t_prev.TOI) + INTERVAL 7 day WHERE t_next.Status= 'Non-Compliant'
– Bharath
Nov 23 '18 at 19:37
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53451101%2fmysql-compare-rows-in-same-table-and-show-distinct%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
http://sqlfiddle.com/#!9/1b0c46/1
SELECT t_next.*
FROM t1 t_prev
JOIN t1 t_next
ON t_next.server = t_prev.server
AND t_next.component = t_prev.component
AND t_next.date = t_prev.date + INTERVAL 1 DAY
AND t_next.status != t_prev.status
WHERE t_prev.date = '2018-01-09'
answered Nov 23 '18 at 18:35
AlexAlex
14.4k11937
Thanks Alex. That worked perfectly. Actually my data is more complex than the example i have given. I have learned from your approach and modified to my need as belowSELECT t_next.Server,t_next.Compliance-StatusFROM cd t_prev JOIN cd t_next ON t_next.Server= t_prev.ServerAND t_next.System-Parameter= t_prev.System-ParameterAND t_next.Compliance-Status!= t_prev.Compliance-StatusAND date(t_next.TOI) = date(t_prev.TOI) + INTERVAL 7 day WHERE t_next.Compliance-Status= 'Non-Compliant'
– Bharath
Nov 23 '18 at 19:31
Thanks, Alex. That worked perfectly. Actually, my data is more complex than the example I have given. I learned from your approach and modified as below to match my requirement.
– Bharath
Nov 23 '18 at 19:37
SELECT t_next.Server,t_next.Status,t_next.'DOR' FROM Table t_prev JOIN Table t_next ON t_next.Server= t_prev.ServerAND t_next.System-Parameter= t_prev.System-ParameterAND t_next.Status!= t_prev.StatusAND date(t_next.TOI) = date(t_prev.TOI) + INTERVAL 7 day WHERE t_next.Status= 'Non-Compliant'
– Bharath
Nov 23 '18 at 19:37
add a comment |
0
http://sqlfiddle.com/#!9/1b0c46/1
SELECT t_next.*
FROM t1 t_prev
JOIN t1 t_next
ON t_next.server = t_prev.server
AND t_next.component = t_prev.component
AND t_next.date = t_prev.date + INTERVAL 1 DAY
AND t_next.status != t_prev.status
WHERE t_prev.date = '2018-01-09'
answered Nov 23 '18 at 18:35
AlexAlex
14.4k11937
Thanks Alex. That worked perfectly. Actually my data is more complex than the example i have given. I have learned from your approach and modified to my need as belowSELECT t_next.Server,t_next.Compliance-StatusFROM cd t_prev JOIN cd t_next ON t_next.Server= t_prev.ServerAND t_next.System-Parameter= t_prev.System-ParameterAND t_next.Compliance-Status!= t_prev.Compliance-StatusAND date(t_next.TOI) = date(t_prev.TOI) + INTERVAL 7 day WHERE t_next.Compliance-Status= 'Non-Compliant'
– Bharath
Nov 23 '18 at 19:31
Thanks, Alex. That worked perfectly. Actually, my data is more complex than the example I have given. I learned from your approach and modified as below to match my requirement.
– Bharath
Nov 23 '18 at 19:37
SELECT t_next.Server,t_next.Status,t_next.'DOR' FROM Table t_prev JOIN Table t_next ON t_next.Server= t_prev.ServerAND t_next.System-Parameter= t_prev.System-ParameterAND t_next.Status!= t_prev.StatusAND date(t_next.TOI) = date(t_prev.TOI) + INTERVAL 7 day WHERE t_next.Status= 'Non-Compliant'
– Bharath
Nov 23 '18 at 19:37
add a comment |
0
0
0
http://sqlfiddle.com/#!9/1b0c46/1
SELECT t_next.*
FROM t1 t_prev
JOIN t1 t_next
ON t_next.server = t_prev.server
AND t_next.component = t_prev.component
AND t_next.date = t_prev.date + INTERVAL 1 DAY
AND t_next.status != t_prev.status
WHERE t_prev.date = '2018-01-09'
answered Nov 23 '18 at 18:35
AlexAlex
14.4k11937
http://sqlfiddle.com/#!9/1b0c46/1
SELECT t_next.*
FROM t1 t_prev
JOIN t1 t_next
ON t_next.server = t_prev.server
AND t_next.component = t_prev.component
AND t_next.date = t_prev.date + INTERVAL 1 DAY
AND t_next.status != t_prev.status
WHERE t_prev.date = '2018-01-09'
answered Nov 23 '18 at 18:35
AlexAlex
14.4k11937
answered Nov 23 '18 at 18:35
AlexAlex
14.4k11937
answered Nov 23 '18 at 18:35
AlexAlex
14.4k11937
answered Nov 23 '18 at 18:35
AlexAlex
14.4k11937
14.4k11937
Thanks Alex. That worked perfectly. Actually my data is more complex than the example i have given. I have learned from your approach and modified to my need as belowSELECT t_next.Server,t_next.Compliance-StatusFROM cd t_prev JOIN cd t_next ON t_next.Server= t_prev.ServerAND t_next.System-Parameter= t_prev.System-ParameterAND t_next.Compliance-Status!= t_prev.Compliance-StatusAND date(t_next.TOI) = date(t_prev.TOI) + INTERVAL 7 day WHERE t_next.Compliance-Status= 'Non-Compliant'
– Bharath
Nov 23 '18 at 19:31
Thanks, Alex. That worked perfectly. Actually, my data is more complex than the example I have given. I learned from your approach and modified as below to match my requirement.
– Bharath
Nov 23 '18 at 19:37
SELECT t_next.Server,t_next.Status,t_next.'DOR' FROM Table t_prev JOIN Table t_next ON t_next.Server= t_prev.ServerAND t_next.System-Parameter= t_prev.System-ParameterAND t_next.Status!= t_prev.StatusAND date(t_next.TOI) = date(t_prev.TOI) + INTERVAL 7 day WHERE t_next.Status= 'Non-Compliant'
– Bharath
Nov 23 '18 at 19:37
add a comment |
Thanks Alex. That worked perfectly. Actually my data is more complex than the example i have given. I have learned from your approach and modified to my need as belowSELECT t_next.Server,t_next.Compliance-StatusFROM cd t_prev JOIN cd t_next ON t_next.Server= t_prev.ServerAND t_next.System-Parameter= t_prev.System-ParameterAND t_next.Compliance-Status!= t_prev.Compliance-StatusAND date(t_next.TOI) = date(t_prev.TOI) + INTERVAL 7 day WHERE t_next.Compliance-Status= 'Non-Compliant'
– Bharath
Nov 23 '18 at 19:31
Thanks, Alex. That worked perfectly. Actually, my data is more complex than the example I have given. I learned from your approach and modified as below to match my requirement.
– Bharath
Nov 23 '18 at 19:37
SELECT t_next.Server,t_next.Status,t_next.'DOR' FROM Table t_prev JOIN Table t_next ON t_next.Server= t_prev.ServerAND t_next.System-Parameter= t_prev.System-ParameterAND t_next.Status!= t_prev.StatusAND date(t_next.TOI) = date(t_prev.TOI) + INTERVAL 7 day WHERE t_next.Status= 'Non-Compliant'
– Bharath
Nov 23 '18 at 19:37
Thanks Alex. That worked perfectly. Actually my data is more complex than the example i have given. I have learned from your approach and modified to my need as belowSELECT t_next.Server,t_next.
Compliance-Status FROM cd t_prev JOIN cd t_next ON t_next.Server = t_prev.Server AND t_next.System-Parameter = t_prev.System-Parameter AND t_next.Compliance-Status != t_prev.Compliance-Status AND date(t_next.TOI) = date(t_prev.TOI) + INTERVAL 7 day WHERE t_next.Compliance-Status = 'Non-Compliant'– Bharath
Nov 23 '18 at 19:31
Thanks Alex. That worked perfectly. Actually my data is more complex than the example i have given. I have learned from your approach and modified to my need as belowSELECT t_next.Server,t_next.
Compliance-Status FROM cd t_prev JOIN cd t_next ON t_next.Server = t_prev.Server AND t_next.System-Parameter = t_prev.System-Parameter AND t_next.Compliance-Status != t_prev.Compliance-Status AND date(t_next.TOI) = date(t_prev.TOI) + INTERVAL 7 day WHERE t_next.Compliance-Status = 'Non-Compliant'– Bharath
Nov 23 '18 at 19:31
Thanks, Alex. That worked perfectly. Actually, my data is more complex than the example I have given. I learned from your approach and modified as below to match my requirement.
– Bharath
Nov 23 '18 at 19:37
Thanks, Alex. That worked perfectly. Actually, my data is more complex than the example I have given. I learned from your approach and modified as below to match my requirement.
– Bharath
Nov 23 '18 at 19:37
SELECT t_next.Server,t_next.
Status,t_next.'DOR' FROM Table t_prev JOIN Table t_next ON t_next.Server = t_prev.Server AND t_next.System-Parameter = t_prev.System-Parameter AND t_next.Status != t_prev.Status AND date(t_next.TOI) = date(t_prev.TOI) + INTERVAL 7 day WHERE t_next.Status = 'Non-Compliant'– Bharath
Nov 23 '18 at 19:37
SELECT t_next.Server,t_next.
Status,t_next.'DOR' FROM Table t_prev JOIN Table t_next ON t_next.Server = t_prev.Server AND t_next.System-Parameter = t_prev.System-Parameter AND t_next.Status != t_prev.Status AND date(t_next.TOI) = date(t_prev.TOI) + INTERVAL 7 day WHERE t_next.Status = 'Non-Compliant'– Bharath
Nov 23 '18 at 19:37
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53451101%2fmysql-compare-rows-in-same-table-and-show-distinct%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Are you using MySQL/MariaDB or SQL Server (TSQL) ? Please dont spam tag all the RDBMS
– Madhur Bhaiya
Nov 23 '18 at 17:53
I am using Mysql, i am new to databases and learning. I thought SQL logic is more or less same with all RDBMS and thought someone could help me learn. Thanks
– Bharath
Nov 23 '18 at 18:00
You have multiple entries with different Status values on same date. This is conflicting with your defined logic.
– Madhur Bhaiya
Nov 23 '18 at 18:00
Each RDBMS uses its own version of SQL flavour. So it is best to specify the exact RDBMS you are using.
– Madhur Bhaiya
Nov 23 '18 at 18:01
Ok, i have edited to reflect MySQL. Thanks
– Bharath
Nov 23 '18 at 18:02