Mysql Code To Mark New And Duplicate Values
2
I have a mysql table (shown below) with some values, I need to get the new values and duplicate values
I tried SELECT *,COUNT(SerialNumber) FROM config_log GROUP BY SerialNumber;
But it return only the duplicate count. How to find this ???
Thank You
Table :
Id SerialNumber ConfigDate ProductId
---+--------------+-----------+---------
1 11111 2018-12-22 1
2 22222 2018-12-22 2
3 33333 2018-12-22 3
4 11111 2018-12-23 1
Product Table :
ProductId Name
-----------+------
1 a
2 b
3 c
Expected Result
Id SerialNumber ConfigDate Type Name
---+--------------+------------+----------+-------
1 11111 2018-12-22 New a
2 22222 2018-12-22 New b
3 33333 2018-12-22 New c
4 11111 2018-12-23 Duplicate a
mysql stored-procedures select-query
asked Jan 2 at 4:46
JIJOMON K.AJIJOMON K.A
1,005523
add a comment |
2
I have a mysql table (shown below) with some values, I need to get the new values and duplicate values
I tried SELECT *,COUNT(SerialNumber) FROM config_log GROUP BY SerialNumber;
But it return only the duplicate count. How to find this ???
Thank You
Table :
Id SerialNumber ConfigDate ProductId
---+--------------+-----------+---------
1 11111 2018-12-22 1
2 22222 2018-12-22 2
3 33333 2018-12-22 3
4 11111 2018-12-23 1
Product Table :
ProductId Name
-----------+------
1 a
2 b
3 c
Expected Result
Id SerialNumber ConfigDate Type Name
---+--------------+------------+----------+-------
1 11111 2018-12-22 New a
2 22222 2018-12-22 New b
3 33333 2018-12-22 New c
4 11111 2018-12-23 Duplicate a
mysql stored-procedures select-query
asked Jan 2 at 4:46
JIJOMON K.AJIJOMON K.A
1,005523
what is your mysql version
– fa06
Jan 2 at 4:47
Server version: 10.1.28-MariaDB
– JIJOMON K.A
Jan 2 at 4:48
add a comment |
2
2
2
1
I have a mysql table (shown below) with some values, I need to get the new values and duplicate values
I tried SELECT *,COUNT(SerialNumber) FROM config_log GROUP BY SerialNumber;
But it return only the duplicate count. How to find this ???
Thank You
Table :
Id SerialNumber ConfigDate ProductId
---+--------------+-----------+---------
1 11111 2018-12-22 1
2 22222 2018-12-22 2
3 33333 2018-12-22 3
4 11111 2018-12-23 1
Product Table :
ProductId Name
-----------+------
1 a
2 b
3 c
Expected Result
Id SerialNumber ConfigDate Type Name
---+--------------+------------+----------+-------
1 11111 2018-12-22 New a
2 22222 2018-12-22 New b
3 33333 2018-12-22 New c
4 11111 2018-12-23 Duplicate a
mysql stored-procedures select-query
asked Jan 2 at 4:46
JIJOMON K.AJIJOMON K.A
1,005523
I have a mysql table (shown below) with some values, I need to get the new values and duplicate values
I tried SELECT *,COUNT(SerialNumber) FROM config_log GROUP BY SerialNumber;
But it return only the duplicate count. How to find this ???
Thank You
Table :
Id SerialNumber ConfigDate ProductId
---+--------------+-----------+---------
1 11111 2018-12-22 1
2 22222 2018-12-22 2
3 33333 2018-12-22 3
4 11111 2018-12-23 1
Product Table :
ProductId Name
-----------+------
1 a
2 b
3 c
Expected Result
Id SerialNumber ConfigDate Type Name
---+--------------+------------+----------+-------
1 11111 2018-12-22 New a
2 22222 2018-12-22 New b
3 33333 2018-12-22 New c
4 11111 2018-12-23 Duplicate a
mysql stored-procedures select-query
mysql stored-procedures select-query
asked Jan 2 at 4:46
JIJOMON K.AJIJOMON K.A
1,005523
asked Jan 2 at 4:46
JIJOMON K.AJIJOMON K.A
1,005523
edited Jan 2 at 5:12
JIJOMON K.A
asked Jan 2 at 4:46
JIJOMON K.AJIJOMON K.A
1,005523
asked Jan 2 at 4:46
JIJOMON K.AJIJOMON K.A
1,005523
asked Jan 2 at 4:46
JIJOMON K.AJIJOMON K.A
1,005523
1,005523
what is your mysql version
– fa06
Jan 2 at 4:47
Server version: 10.1.28-MariaDB
– JIJOMON K.A
Jan 2 at 4:48
add a comment |
what is your mysql version
– fa06
Jan 2 at 4:47
Server version: 10.1.28-MariaDB
– JIJOMON K.A
Jan 2 at 4:48
what is your mysql version
– fa06
Jan 2 at 4:47
what is your mysql version
– fa06
Jan 2 at 4:47
Server version: 10.1.28-MariaDB
– JIJOMON K.A
Jan 2 at 4:48
Server version: 10.1.28-MariaDB
– JIJOMON K.A
Jan 2 at 4:48
add a comment |
3 Answers
3
active
oldest
votes
4
You can try below -
DEMO
select id,SerialNumber,ConfigDate, case when num=1 then 'New' else 'Duplicate' end as Type,
productid,name
from
(
SELECT
@row_number:=CASE
WHEN @SerialNumber = SerialNumber THEN @row_number + 1
ELSE 1
END AS num,
@SerialNumber:=SerialNumber as PSerialNumber, id, SerialNumber,ConfigDate,productid,name
FROM
t1 left join product on productid=pid ,(SELECT @SerialNumber:=0,@row_number:=0) AS t
order by SerialNumber
)A
answered Jan 2 at 4:52
fa06fa06
15.6k21018
1
Thanks Bro. its Working, I need to join another table with this how its possible ???
– JIJOMON K.A
Jan 2 at 5:05
@JIJOMONK.A, could you please add that table to your quesiton
– fa06
Jan 2 at 5:09
@JIJOMONK.A, check here - dbfiddle.uk/…
– fa06
Jan 2 at 5:16
1
Thanks Bro Its Working. Thanks You for the support.
– JIJOMON K.A
Jan 2 at 5:32
add a comment |
1
the following mysql query will produce the expected result
SELECT cl.*,
IF (cl.id = (SELECT Min(id)
FROM `config_log`
WHERE cl.serialnumber = serialnumber
ORDER BY id ASC), 'NEW', 'DUPLICATE') AS type,
p.name
FROM `config_log` AS `cl`
INNER JOIN products AS p
ON p.id = cl.productid
Tested on mysql Distrib 5.7.24, for Linux (x86_64)
answered Jan 2 at 5:23
virtualonevirtualone
763
Thanks Bro Its Working
– JIJOMON K.A
Jan 2 at 6:31
how to write where condition like type='NEW' or type='DUPLICATE'
– JIJOMON K.A
Jan 7 at 4:54
you can add having in end like this " HAVING type = 'DUPLICATE' ".. It may sound not logical to add having without group by but its works .. not the best solution ... its better to redo the query if you have different requirements
– virtualone
Jan 7 at 5:21
add a comment |
1
for filtering the result you can use having like this
SELECT cl.*,
IF (cl.id = (SELECT Min(id)
FROM `config_log`
WHERE cl.serialnumber = serialnumber
ORDER BY id ASC), 'NEW', 'DUPLICATE') AS type,
p.name
FROM `config_log` AS `cl`
INNER JOIN products AS p
ON p.id = cl.productid
HAVING type = 'DUPLICATE'
answered Jan 7 at 5:33
virtualonevirtualone
763
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%2f54001310%2fmysql-code-to-mark-new-and-duplicate-values%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
4
You can try below -
DEMO
select id,SerialNumber,ConfigDate, case when num=1 then 'New' else 'Duplicate' end as Type,
productid,name
from
(
SELECT
@row_number:=CASE
WHEN @SerialNumber = SerialNumber THEN @row_number + 1
ELSE 1
END AS num,
@SerialNumber:=SerialNumber as PSerialNumber, id, SerialNumber,ConfigDate,productid,name
FROM
t1 left join product on productid=pid ,(SELECT @SerialNumber:=0,@row_number:=0) AS t
order by SerialNumber
)A
answered Jan 2 at 4:52
fa06fa06
15.6k21018
1
Thanks Bro. its Working, I need to join another table with this how its possible ???
– JIJOMON K.A
Jan 2 at 5:05
@JIJOMONK.A, could you please add that table to your quesiton
– fa06
Jan 2 at 5:09
@JIJOMONK.A, check here - dbfiddle.uk/…
– fa06
Jan 2 at 5:16
1
Thanks Bro Its Working. Thanks You for the support.
– JIJOMON K.A
Jan 2 at 5:32
add a comment |
4
You can try below -
DEMO
select id,SerialNumber,ConfigDate, case when num=1 then 'New' else 'Duplicate' end as Type,
productid,name
from
(
SELECT
@row_number:=CASE
WHEN @SerialNumber = SerialNumber THEN @row_number + 1
ELSE 1
END AS num,
@SerialNumber:=SerialNumber as PSerialNumber, id, SerialNumber,ConfigDate,productid,name
FROM
t1 left join product on productid=pid ,(SELECT @SerialNumber:=0,@row_number:=0) AS t
order by SerialNumber
)A
answered Jan 2 at 4:52
fa06fa06
15.6k21018
1
Thanks Bro. its Working, I need to join another table with this how its possible ???
– JIJOMON K.A
Jan 2 at 5:05
@JIJOMONK.A, could you please add that table to your quesiton
– fa06
Jan 2 at 5:09
@JIJOMONK.A, check here - dbfiddle.uk/…
– fa06
Jan 2 at 5:16
1
Thanks Bro Its Working. Thanks You for the support.
– JIJOMON K.A
Jan 2 at 5:32
add a comment |
4
4
4
You can try below -
DEMO
select id,SerialNumber,ConfigDate, case when num=1 then 'New' else 'Duplicate' end as Type,
productid,name
from
(
SELECT
@row_number:=CASE
WHEN @SerialNumber = SerialNumber THEN @row_number + 1
ELSE 1
END AS num,
@SerialNumber:=SerialNumber as PSerialNumber, id, SerialNumber,ConfigDate,productid,name
FROM
t1 left join product on productid=pid ,(SELECT @SerialNumber:=0,@row_number:=0) AS t
order by SerialNumber
)A
answered Jan 2 at 4:52
fa06fa06
15.6k21018
You can try below -
DEMO
select id,SerialNumber,ConfigDate, case when num=1 then 'New' else 'Duplicate' end as Type,
productid,name
from
(
SELECT
@row_number:=CASE
WHEN @SerialNumber = SerialNumber THEN @row_number + 1
ELSE 1
END AS num,
@SerialNumber:=SerialNumber as PSerialNumber, id, SerialNumber,ConfigDate,productid,name
FROM
t1 left join product on productid=pid ,(SELECT @SerialNumber:=0,@row_number:=0) AS t
order by SerialNumber
)A
answered Jan 2 at 4:52
fa06fa06
15.6k21018
edited Jan 2 at 5:17
answered Jan 2 at 4:52
fa06fa06
15.6k21018
answered Jan 2 at 4:52
fa06fa06
15.6k21018
answered Jan 2 at 4:52
fa06fa06
15.6k21018
15.6k21018
1
Thanks Bro. its Working, I need to join another table with this how its possible ???
– JIJOMON K.A
Jan 2 at 5:05
@JIJOMONK.A, could you please add that table to your quesiton
– fa06
Jan 2 at 5:09
@JIJOMONK.A, check here - dbfiddle.uk/…
– fa06
Jan 2 at 5:16
1
Thanks Bro Its Working. Thanks You for the support.
– JIJOMON K.A
Jan 2 at 5:32
add a comment |
1
Thanks Bro. its Working, I need to join another table with this how its possible ???
– JIJOMON K.A
Jan 2 at 5:05
@JIJOMONK.A, could you please add that table to your quesiton
– fa06
Jan 2 at 5:09
@JIJOMONK.A, check here - dbfiddle.uk/…
– fa06
Jan 2 at 5:16
1
Thanks Bro Its Working. Thanks You for the support.
– JIJOMON K.A
Jan 2 at 5:32
1
1
Thanks Bro. its Working, I need to join another table with this how its possible ???
– JIJOMON K.A
Jan 2 at 5:05
Thanks Bro. its Working, I need to join another table with this how its possible ???
– JIJOMON K.A
Jan 2 at 5:05
@JIJOMONK.A, could you please add that table to your quesiton
– fa06
Jan 2 at 5:09
@JIJOMONK.A, could you please add that table to your quesiton
– fa06
Jan 2 at 5:09
@JIJOMONK.A, check here - dbfiddle.uk/…
– fa06
Jan 2 at 5:16
@JIJOMONK.A, check here - dbfiddle.uk/…
– fa06
Jan 2 at 5:16
1
1
Thanks Bro Its Working. Thanks You for the support.
– JIJOMON K.A
Jan 2 at 5:32
Thanks Bro Its Working. Thanks You for the support.
– JIJOMON K.A
Jan 2 at 5:32
add a comment |
1
the following mysql query will produce the expected result
SELECT cl.*,
IF (cl.id = (SELECT Min(id)
FROM `config_log`
WHERE cl.serialnumber = serialnumber
ORDER BY id ASC), 'NEW', 'DUPLICATE') AS type,
p.name
FROM `config_log` AS `cl`
INNER JOIN products AS p
ON p.id = cl.productid
Tested on mysql Distrib 5.7.24, for Linux (x86_64)
answered Jan 2 at 5:23
virtualonevirtualone
763
Thanks Bro Its Working
– JIJOMON K.A
Jan 2 at 6:31
how to write where condition like type='NEW' or type='DUPLICATE'
– JIJOMON K.A
Jan 7 at 4:54
you can add having in end like this " HAVING type = 'DUPLICATE' ".. It may sound not logical to add having without group by but its works .. not the best solution ... its better to redo the query if you have different requirements
– virtualone
Jan 7 at 5:21
add a comment |
1
the following mysql query will produce the expected result
SELECT cl.*,
IF (cl.id = (SELECT Min(id)
FROM `config_log`
WHERE cl.serialnumber = serialnumber
ORDER BY id ASC), 'NEW', 'DUPLICATE') AS type,
p.name
FROM `config_log` AS `cl`
INNER JOIN products AS p
ON p.id = cl.productid
Tested on mysql Distrib 5.7.24, for Linux (x86_64)
answered Jan 2 at 5:23
virtualonevirtualone
763
Thanks Bro Its Working
– JIJOMON K.A
Jan 2 at 6:31
how to write where condition like type='NEW' or type='DUPLICATE'
– JIJOMON K.A
Jan 7 at 4:54
you can add having in end like this " HAVING type = 'DUPLICATE' ".. It may sound not logical to add having without group by but its works .. not the best solution ... its better to redo the query if you have different requirements
– virtualone
Jan 7 at 5:21
add a comment |
1
1
1
the following mysql query will produce the expected result
SELECT cl.*,
IF (cl.id = (SELECT Min(id)
FROM `config_log`
WHERE cl.serialnumber = serialnumber
ORDER BY id ASC), 'NEW', 'DUPLICATE') AS type,
p.name
FROM `config_log` AS `cl`
INNER JOIN products AS p
ON p.id = cl.productid
Tested on mysql Distrib 5.7.24, for Linux (x86_64)
answered Jan 2 at 5:23
virtualonevirtualone
763
the following mysql query will produce the expected result
SELECT cl.*,
IF (cl.id = (SELECT Min(id)
FROM `config_log`
WHERE cl.serialnumber = serialnumber
ORDER BY id ASC), 'NEW', 'DUPLICATE') AS type,
p.name
FROM `config_log` AS `cl`
INNER JOIN products AS p
ON p.id = cl.productid
Tested on mysql Distrib 5.7.24, for Linux (x86_64)
answered Jan 2 at 5:23
virtualonevirtualone
763
edited Jan 2 at 5:43
answered Jan 2 at 5:23
virtualonevirtualone
763
answered Jan 2 at 5:23
virtualonevirtualone
763
answered Jan 2 at 5:23
virtualonevirtualone
763
763
Thanks Bro Its Working
– JIJOMON K.A
Jan 2 at 6:31
how to write where condition like type='NEW' or type='DUPLICATE'
– JIJOMON K.A
Jan 7 at 4:54
you can add having in end like this " HAVING type = 'DUPLICATE' ".. It may sound not logical to add having without group by but its works .. not the best solution ... its better to redo the query if you have different requirements
– virtualone
Jan 7 at 5:21
add a comment |
Thanks Bro Its Working
– JIJOMON K.A
Jan 2 at 6:31
how to write where condition like type='NEW' or type='DUPLICATE'
– JIJOMON K.A
Jan 7 at 4:54
you can add having in end like this " HAVING type = 'DUPLICATE' ".. It may sound not logical to add having without group by but its works .. not the best solution ... its better to redo the query if you have different requirements
– virtualone
Jan 7 at 5:21
Thanks Bro Its Working
– JIJOMON K.A
Jan 2 at 6:31
Thanks Bro Its Working
– JIJOMON K.A
Jan 2 at 6:31
how to write where condition like type='NEW' or type='DUPLICATE'
– JIJOMON K.A
Jan 7 at 4:54
how to write where condition like type='NEW' or type='DUPLICATE'
– JIJOMON K.A
Jan 7 at 4:54
you can add having in end like this " HAVING type = 'DUPLICATE' ".. It may sound not logical to add having without group by but its works .. not the best solution ... its better to redo the query if you have different requirements
– virtualone
Jan 7 at 5:21
you can add having in end like this " HAVING type = 'DUPLICATE' ".. It may sound not logical to add having without group by but its works .. not the best solution ... its better to redo the query if you have different requirements
– virtualone
Jan 7 at 5:21
add a comment |
1
for filtering the result you can use having like this
SELECT cl.*,
IF (cl.id = (SELECT Min(id)
FROM `config_log`
WHERE cl.serialnumber = serialnumber
ORDER BY id ASC), 'NEW', 'DUPLICATE') AS type,
p.name
FROM `config_log` AS `cl`
INNER JOIN products AS p
ON p.id = cl.productid
HAVING type = 'DUPLICATE'
answered Jan 7 at 5:33
virtualonevirtualone
763
add a comment |
1
for filtering the result you can use having like this
SELECT cl.*,
IF (cl.id = (SELECT Min(id)
FROM `config_log`
WHERE cl.serialnumber = serialnumber
ORDER BY id ASC), 'NEW', 'DUPLICATE') AS type,
p.name
FROM `config_log` AS `cl`
INNER JOIN products AS p
ON p.id = cl.productid
HAVING type = 'DUPLICATE'
answered Jan 7 at 5:33
virtualonevirtualone
763
add a comment |
1
1
1
for filtering the result you can use having like this
SELECT cl.*,
IF (cl.id = (SELECT Min(id)
FROM `config_log`
WHERE cl.serialnumber = serialnumber
ORDER BY id ASC), 'NEW', 'DUPLICATE') AS type,
p.name
FROM `config_log` AS `cl`
INNER JOIN products AS p
ON p.id = cl.productid
HAVING type = 'DUPLICATE'
answered Jan 7 at 5:33
virtualonevirtualone
763
for filtering the result you can use having like this
SELECT cl.*,
IF (cl.id = (SELECT Min(id)
FROM `config_log`
WHERE cl.serialnumber = serialnumber
ORDER BY id ASC), 'NEW', 'DUPLICATE') AS type,
p.name
FROM `config_log` AS `cl`
INNER JOIN products AS p
ON p.id = cl.productid
HAVING type = 'DUPLICATE'
answered Jan 7 at 5:33
virtualonevirtualone
763
answered Jan 7 at 5:33
virtualonevirtualone
763
answered Jan 7 at 5:33
virtualonevirtualone
763
answered Jan 7 at 5:33
virtualonevirtualone
763
763
add a comment |
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%2f54001310%2fmysql-code-to-mark-new-and-duplicate-values%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
what is your mysql version
– fa06
Jan 2 at 4:47
Server version: 10.1.28-MariaDB
– JIJOMON K.A
Jan 2 at 4:48