Select non-duplicate records in a MySQL table column
up vote
0
down vote
favorite
I have a large table with just 2 column. One is the primary id column and other is a data column.
I need to select only the records that is not-duplicated in the table. I tried the below query but it takes much time and not sure if it really work.
select * from (select column_name
from table_name
group by column_name
having count(*) = 1) x;
What do you think?
I am also open to other tries if it will do the job faster.
mysql mysql-workbench
edited Nov 22 at 16:35
Madhur Bhaiya
19.2k62236
asked Nov 22 at 16:33
Ahmed Maher
227
add a comment |
up vote
0
down vote
favorite
I have a large table with just 2 column. One is the primary id column and other is a data column.
I need to select only the records that is not-duplicated in the table. I tried the below query but it takes much time and not sure if it really work.
select * from (select column_name
from table_name
group by column_name
having count(*) = 1) x;
What do you think?
I am also open to other tries if it will do the job faster.
mysql mysql-workbench
edited Nov 22 at 16:35
Madhur Bhaiya
19.2k62236
asked Nov 22 at 16:33
Ahmed Maher
227
Is the second columncolumn_nameindexed in your table ?
– Madhur Bhaiya
Nov 22 at 16:36
2
Not sure why you need to select from that sub-query. Just using the sql from the sub-query would already be enough?
– LukStorms
Nov 22 at 16:39
You mean select column_name from table_name group by column_name having count(*) = 1; ??
– Ahmed Maher
Nov 22 at 16:43
@AhmedMaher yes
– Madhur Bhaiya
Nov 22 at 16:46
Yes second column column_name is indexed.
– Ahmed Maher
Nov 22 at 16:47
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have a large table with just 2 column. One is the primary id column and other is a data column.
I need to select only the records that is not-duplicated in the table. I tried the below query but it takes much time and not sure if it really work.
select * from (select column_name
from table_name
group by column_name
having count(*) = 1) x;
What do you think?
I am also open to other tries if it will do the job faster.
mysql mysql-workbench
edited Nov 22 at 16:35
Madhur Bhaiya
19.2k62236
asked Nov 22 at 16:33
Ahmed Maher
227
I have a large table with just 2 column. One is the primary id column and other is a data column.
I need to select only the records that is not-duplicated in the table. I tried the below query but it takes much time and not sure if it really work.
select * from (select column_name
from table_name
group by column_name
having count(*) = 1) x;
What do you think?
I am also open to other tries if it will do the job faster.
mysql mysql-workbench
mysql mysql-workbench
edited Nov 22 at 16:35
Madhur Bhaiya
19.2k62236
asked Nov 22 at 16:33
Ahmed Maher
227
edited Nov 22 at 16:35
Madhur Bhaiya
19.2k62236
asked Nov 22 at 16:33
Ahmed Maher
227
edited Nov 22 at 16:35
Madhur Bhaiya
19.2k62236
edited Nov 22 at 16:35
Madhur Bhaiya
19.2k62236
edited Nov 22 at 16:35
Madhur Bhaiya
19.2k62236
19.2k62236
asked Nov 22 at 16:33
Ahmed Maher
227
asked Nov 22 at 16:33
Ahmed Maher
227
asked Nov 22 at 16:33
Ahmed Maher
227
227
Is the second columncolumn_nameindexed in your table ?
– Madhur Bhaiya
Nov 22 at 16:36
2
Not sure why you need to select from that sub-query. Just using the sql from the sub-query would already be enough?
– LukStorms
Nov 22 at 16:39
You mean select column_name from table_name group by column_name having count(*) = 1; ??
– Ahmed Maher
Nov 22 at 16:43
@AhmedMaher yes
– Madhur Bhaiya
Nov 22 at 16:46
Yes second column column_name is indexed.
– Ahmed Maher
Nov 22 at 16:47
add a comment |
Is the second columncolumn_nameindexed in your table ?
– Madhur Bhaiya
Nov 22 at 16:36
2
Not sure why you need to select from that sub-query. Just using the sql from the sub-query would already be enough?
– LukStorms
Nov 22 at 16:39
You mean select column_name from table_name group by column_name having count(*) = 1; ??
– Ahmed Maher
Nov 22 at 16:43
@AhmedMaher yes
– Madhur Bhaiya
Nov 22 at 16:46
Yes second column column_name is indexed.
– Ahmed Maher
Nov 22 at 16:47
Is the second column
column_name indexed in your table ?– Madhur Bhaiya
Nov 22 at 16:36
Is the second column
column_name indexed in your table ?– Madhur Bhaiya
Nov 22 at 16:36
2
2
Not sure why you need to select from that sub-query. Just using the sql from the sub-query would already be enough?
– LukStorms
Nov 22 at 16:39
Not sure why you need to select from that sub-query. Just using the sql from the sub-query would already be enough?
– LukStorms
Nov 22 at 16:39
You mean select column_name from table_name group by column_name having count(*) = 1; ??
– Ahmed Maher
Nov 22 at 16:43
You mean select column_name from table_name group by column_name having count(*) = 1; ??
– Ahmed Maher
Nov 22 at 16:43
@AhmedMaher yes
– Madhur Bhaiya
Nov 22 at 16:46
@AhmedMaher yes
– Madhur Bhaiya
Nov 22 at 16:46
Yes second column column_name is indexed.
– Ahmed Maher
Nov 22 at 16:47
Yes second column column_name is indexed.
– Ahmed Maher
Nov 22 at 16:47
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
You can left join the same table or use subquery to check for duplicates.
This should be easier for SQL server as it would not count all duplicates.
Something like this:
SELECT
t1.column_name
FROM
table_name AS t1
WHERE
NOT EXISTS (
SELECT
*
FROM
table_name AS t2
WHERE
t2.column_name = t1.column_name
AND t2.id != t1.id
)
OR
SELECT
t1.column_name
FROM
table_name AS t1
LEFT JOIN table_name t2 ON (
t2.column_name = t1.column_name
t2.id != t1.id
)
WHERE
t2.column.name IS NULL
answered Nov 23 at 0:35
fifonik
28815
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',
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
});
}
});
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%2f53435093%2fselect-non-duplicate-records-in-a-mysql-table-column%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
up vote
0
down vote
You can left join the same table or use subquery to check for duplicates.
This should be easier for SQL server as it would not count all duplicates.
Something like this:
SELECT
t1.column_name
FROM
table_name AS t1
WHERE
NOT EXISTS (
SELECT
*
FROM
table_name AS t2
WHERE
t2.column_name = t1.column_name
AND t2.id != t1.id
)
OR
SELECT
t1.column_name
FROM
table_name AS t1
LEFT JOIN table_name t2 ON (
t2.column_name = t1.column_name
t2.id != t1.id
)
WHERE
t2.column.name IS NULL
answered Nov 23 at 0:35
fifonik
28815
add a comment |
up vote
0
down vote
You can left join the same table or use subquery to check for duplicates.
This should be easier for SQL server as it would not count all duplicates.
Something like this:
SELECT
t1.column_name
FROM
table_name AS t1
WHERE
NOT EXISTS (
SELECT
*
FROM
table_name AS t2
WHERE
t2.column_name = t1.column_name
AND t2.id != t1.id
)
OR
SELECT
t1.column_name
FROM
table_name AS t1
LEFT JOIN table_name t2 ON (
t2.column_name = t1.column_name
t2.id != t1.id
)
WHERE
t2.column.name IS NULL
answered Nov 23 at 0:35
fifonik
28815
add a comment |
up vote
0
down vote
up vote
0
down vote
You can left join the same table or use subquery to check for duplicates.
This should be easier for SQL server as it would not count all duplicates.
Something like this:
SELECT
t1.column_name
FROM
table_name AS t1
WHERE
NOT EXISTS (
SELECT
*
FROM
table_name AS t2
WHERE
t2.column_name = t1.column_name
AND t2.id != t1.id
)
OR
SELECT
t1.column_name
FROM
table_name AS t1
LEFT JOIN table_name t2 ON (
t2.column_name = t1.column_name
t2.id != t1.id
)
WHERE
t2.column.name IS NULL
answered Nov 23 at 0:35
fifonik
28815
You can left join the same table or use subquery to check for duplicates.
This should be easier for SQL server as it would not count all duplicates.
Something like this:
SELECT
t1.column_name
FROM
table_name AS t1
WHERE
NOT EXISTS (
SELECT
*
FROM
table_name AS t2
WHERE
t2.column_name = t1.column_name
AND t2.id != t1.id
)
OR
SELECT
t1.column_name
FROM
table_name AS t1
LEFT JOIN table_name t2 ON (
t2.column_name = t1.column_name
t2.id != t1.id
)
WHERE
t2.column.name IS NULL
answered Nov 23 at 0:35
fifonik
28815
answered Nov 23 at 0:35
fifonik
28815
answered Nov 23 at 0:35
fifonik
28815
answered Nov 23 at 0:35
fifonik
28815
28815
add a comment |
add a comment |
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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.
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%2f53435093%2fselect-non-duplicate-records-in-a-mysql-table-column%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
Is the second column
column_nameindexed in your table ?– Madhur Bhaiya
Nov 22 at 16:36
2
Not sure why you need to select from that sub-query. Just using the sql from the sub-query would already be enough?
– LukStorms
Nov 22 at 16:39
You mean select column_name from table_name group by column_name having count(*) = 1; ??
– Ahmed Maher
Nov 22 at 16:43
@AhmedMaher yes
– Madhur Bhaiya
Nov 22 at 16:46
Yes second column column_name is indexed.
– Ahmed Maher
Nov 22 at 16:47