Select non-duplicate records in a MySQL table column
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
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 '18 at 16:35
Chowkidar Madhur Bhaiya
19.8k62336
asked Nov 22 '18 at 16:33
Ahmed MaherAhmed Maher
277
add a comment |
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 '18 at 16:35
Chowkidar Madhur Bhaiya
19.8k62336
asked Nov 22 '18 at 16:33
Ahmed MaherAhmed Maher
277
Is the second columncolumn_nameindexed in your table ?
– Chowkidar Madhur Bhaiya
Nov 22 '18 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 '18 at 16:39
You mean select column_name from table_name group by column_name having count(*) = 1; ??
– Ahmed Maher
Nov 22 '18 at 16:43
@AhmedMaher yes
– Chowkidar Madhur Bhaiya
Nov 22 '18 at 16:46
Yes second column column_name is indexed.
– Ahmed Maher
Nov 22 '18 at 16:47
add a comment |
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 '18 at 16:35
Chowkidar Madhur Bhaiya
19.8k62336
asked Nov 22 '18 at 16:33
Ahmed MaherAhmed Maher
277
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 '18 at 16:35
Chowkidar Madhur Bhaiya
19.8k62336
asked Nov 22 '18 at 16:33
Ahmed MaherAhmed Maher
277
edited Nov 22 '18 at 16:35
Chowkidar Madhur Bhaiya
19.8k62336
asked Nov 22 '18 at 16:33
Ahmed MaherAhmed Maher
277
edited Nov 22 '18 at 16:35
Chowkidar Madhur Bhaiya
19.8k62336
edited Nov 22 '18 at 16:35
Chowkidar Madhur Bhaiya
19.8k62336
edited Nov 22 '18 at 16:35
Chowkidar Madhur Bhaiya
19.8k62336
19.8k62336
asked Nov 22 '18 at 16:33
Ahmed MaherAhmed Maher
277
asked Nov 22 '18 at 16:33
Ahmed MaherAhmed Maher
277
asked Nov 22 '18 at 16:33
Ahmed MaherAhmed Maher
277
277
Is the second columncolumn_nameindexed in your table ?
– Chowkidar Madhur Bhaiya
Nov 22 '18 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 '18 at 16:39
You mean select column_name from table_name group by column_name having count(*) = 1; ??
– Ahmed Maher
Nov 22 '18 at 16:43
@AhmedMaher yes
– Chowkidar Madhur Bhaiya
Nov 22 '18 at 16:46
Yes second column column_name is indexed.
– Ahmed Maher
Nov 22 '18 at 16:47
add a comment |
Is the second columncolumn_nameindexed in your table ?
– Chowkidar Madhur Bhaiya
Nov 22 '18 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 '18 at 16:39
You mean select column_name from table_name group by column_name having count(*) = 1; ??
– Ahmed Maher
Nov 22 '18 at 16:43
@AhmedMaher yes
– Chowkidar Madhur Bhaiya
Nov 22 '18 at 16:46
Yes second column column_name is indexed.
– Ahmed Maher
Nov 22 '18 at 16:47
Is the second column
column_name indexed in your table ?– Chowkidar Madhur Bhaiya
Nov 22 '18 at 16:36
Is the second column
column_name indexed in your table ?– Chowkidar Madhur Bhaiya
Nov 22 '18 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 '18 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 '18 at 16:39
You mean select column_name from table_name group by column_name having count(*) = 1; ??
– Ahmed Maher
Nov 22 '18 at 16:43
You mean select column_name from table_name group by column_name having count(*) = 1; ??
– Ahmed Maher
Nov 22 '18 at 16:43
@AhmedMaher yes
– Chowkidar Madhur Bhaiya
Nov 22 '18 at 16:46
@AhmedMaher yes
– Chowkidar Madhur Bhaiya
Nov 22 '18 at 16:46
Yes second column column_name is indexed.
– Ahmed Maher
Nov 22 '18 at 16:47
Yes second column column_name is indexed.
– Ahmed Maher
Nov 22 '18 at 16:47
add a comment |
1 Answer
1
active
oldest
votes
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 '18 at 0:35
fifonikfifonik
728414
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
});
}
});
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
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 '18 at 0:35
fifonikfifonik
728414
add a comment |
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 '18 at 0:35
fifonikfifonik
728414
add a comment |
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 '18 at 0:35
fifonikfifonik
728414
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 '18 at 0:35
fifonikfifonik
728414
answered Nov 23 '18 at 0:35
fifonikfifonik
728414
answered Nov 23 '18 at 0:35
fifonikfifonik
728414
answered Nov 23 '18 at 0:35
fifonikfifonik
728414
728414
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.
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 ?– Chowkidar Madhur Bhaiya
Nov 22 '18 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 '18 at 16:39
You mean select column_name from table_name group by column_name having count(*) = 1; ??
– Ahmed Maher
Nov 22 '18 at 16:43
@AhmedMaher yes
– Chowkidar Madhur Bhaiya
Nov 22 '18 at 16:46
Yes second column column_name is indexed.
– Ahmed Maher
Nov 22 '18 at 16:47