Mixing
SQL Server: retrieve 1 value from a duplicate
I have trouble retrieving max 1 value from each duplicate. It works fine when I don't have Name included in the query, but when I want name in, it retrieves duplicate rows, even when I'm using the MAX function.
Here is an example:
SELECT
b.Name, MAX(a.receiver), a.DATE1 = MIN(a.DATE1)
FROM
Table1 a
INNER JOIN
Table2 b ON a.receiver = b.account
WHERE
Company IN (1,2,3,4,5)
GROUP BY
RECEIVER, b.name
Result:
NAME | Receiver | DATE1
Apple | 12345 | 2018-01-18
Apple A/S | 12345 | 2018-01-19
GDG | 54544 | 2018-01-20
BNS | 54455 | 2018-01-23
Expected result:
NAME | Receiver | DATE1
Apple | 12345 | 2018-01-18
GDG | 54544 | 2018-01-20
BNS | 54455 | 2018-01-23
sql
sql-server tsql edited Nov 19 '18 at 14:29
marc_s
571k12811031251
asked Nov 19 '18 at 14:11
MishMish
317313
|
show 2 more comments
I have trouble retrieving max 1 value from each duplicate. It works fine when I don't have Name included in the query, but when I want name in, it retrieves duplicate rows, even when I'm using the MAX function.
Here is an example:
SELECT
b.Name, MAX(a.receiver), a.DATE1 = MIN(a.DATE1)
FROM
Table1 a
INNER JOIN
Table2 b ON a.receiver = b.account
WHERE
Company IN (1,2,3,4,5)
GROUP BY
RECEIVER, b.name
Result:
NAME | Receiver | DATE1
Apple | 12345 | 2018-01-18
Apple A/S | 12345 | 2018-01-19
GDG | 54544 | 2018-01-20
BNS | 54455 | 2018-01-23
Expected result:
NAME | Receiver | DATE1
Apple | 12345 | 2018-01-18
GDG | 54544 | 2018-01-20
BNS | 54455 | 2018-01-23
sql
sql-server tsql edited Nov 19 '18 at 14:29
marc_s
571k12811031251
asked Nov 19 '18 at 14:11
MishMish
317313
2
Is there any reason Apple and Apple A/S needs to be considered as same name?
– Santhana
Nov 19 '18 at 14:17
Yes, sometimes different users from the same company input different info when submitting for example a form.
– MishMish
Nov 19 '18 at 14:19
then that is data cleaning issue (long sustained way); for now you can handle easiest way via CASE statement
– junketsu
Nov 19 '18 at 14:21
Select name = case when b.name like '%apple%' then 'apple' else b.name end ...and rest of your col.
– junketsu
Nov 19 '18 at 14:22
Have a look at your data. It doesn't make sense that there are two different names for the same account
– holder
Nov 19 '18 at 14:23
|
show 2 more comments
I have trouble retrieving max 1 value from each duplicate. It works fine when I don't have Name included in the query, but when I want name in, it retrieves duplicate rows, even when I'm using the MAX function.
Here is an example:
SELECT
b.Name, MAX(a.receiver), a.DATE1 = MIN(a.DATE1)
FROM
Table1 a
INNER JOIN
Table2 b ON a.receiver = b.account
WHERE
Company IN (1,2,3,4,5)
GROUP BY
RECEIVER, b.name
Result:
NAME | Receiver | DATE1
Apple | 12345 | 2018-01-18
Apple A/S | 12345 | 2018-01-19
GDG | 54544 | 2018-01-20
BNS | 54455 | 2018-01-23
Expected result:
NAME | Receiver | DATE1
Apple | 12345 | 2018-01-18
GDG | 54544 | 2018-01-20
BNS | 54455 | 2018-01-23
sql
sql-server tsql edited Nov 19 '18 at 14:29
marc_s
571k12811031251
asked Nov 19 '18 at 14:11
MishMish
317313
I have trouble retrieving max 1 value from each duplicate. It works fine when I don't have Name included in the query, but when I want name in, it retrieves duplicate rows, even when I'm using the MAX function.
Here is an example:
SELECT
b.Name, MAX(a.receiver), a.DATE1 = MIN(a.DATE1)
FROM
Table1 a
INNER JOIN
Table2 b ON a.receiver = b.account
WHERE
Company IN (1,2,3,4,5)
GROUP BY
RECEIVER, b.name
Result:
NAME | Receiver | DATE1
Apple | 12345 | 2018-01-18
Apple A/S | 12345 | 2018-01-19
GDG | 54544 | 2018-01-20
BNS | 54455 | 2018-01-23
Expected result:
NAME | Receiver | DATE1
Apple | 12345 | 2018-01-18
GDG | 54544 | 2018-01-20
BNS | 54455 | 2018-01-23
sql
sql-server tsql sql
sql-server tsql edited Nov 19 '18 at 14:29
marc_s
571k12811031251
asked Nov 19 '18 at 14:11
MishMish
317313
edited Nov 19 '18 at 14:29
marc_s
571k12811031251
asked Nov 19 '18 at 14:11
MishMish
317313
edited Nov 19 '18 at 14:29
marc_s
571k12811031251
edited Nov 19 '18 at 14:29
marc_s
571k12811031251
edited Nov 19 '18 at 14:29
marc_s
571k12811031251
571k12811031251
asked Nov 19 '18 at 14:11
MishMish
317313
asked Nov 19 '18 at 14:11
MishMish
317313
asked Nov 19 '18 at 14:11
MishMish
317313
317313
2
Is there any reason Apple and Apple A/S needs to be considered as same name?
– Santhana
Nov 19 '18 at 14:17
Yes, sometimes different users from the same company input different info when submitting for example a form.
– MishMish
Nov 19 '18 at 14:19
then that is data cleaning issue (long sustained way); for now you can handle easiest way via CASE statement
– junketsu
Nov 19 '18 at 14:21
Select name = case when b.name like '%apple%' then 'apple' else b.name end ...and rest of your col.
– junketsu
Nov 19 '18 at 14:22
Have a look at your data. It doesn't make sense that there are two different names for the same account
– holder
Nov 19 '18 at 14:23
|
show 2 more comments
2
Is there any reason Apple and Apple A/S needs to be considered as same name?
– Santhana
Nov 19 '18 at 14:17
Yes, sometimes different users from the same company input different info when submitting for example a form.
– MishMish
Nov 19 '18 at 14:19
then that is data cleaning issue (long sustained way); for now you can handle easiest way via CASE statement
– junketsu
Nov 19 '18 at 14:21
Select name = case when b.name like '%apple%' then 'apple' else b.name end ...and rest of your col.
– junketsu
Nov 19 '18 at 14:22
Have a look at your data. It doesn't make sense that there are two different names for the same account
– holder
Nov 19 '18 at 14:23
2
2
Is there any reason Apple and Apple A/S needs to be considered as same name?
– Santhana
Nov 19 '18 at 14:17
Is there any reason Apple and Apple A/S needs to be considered as same name?
– Santhana
Nov 19 '18 at 14:17
Yes, sometimes different users from the same company input different info when submitting for example a form.
– MishMish
Nov 19 '18 at 14:19
Yes, sometimes different users from the same company input different info when submitting for example a form.
– MishMish
Nov 19 '18 at 14:19
then that is data cleaning issue (long sustained way); for now you can handle easiest way via CASE statement
– junketsu
Nov 19 '18 at 14:21
then that is data cleaning issue (long sustained way); for now you can handle easiest way via CASE statement
– junketsu
Nov 19 '18 at 14:21
Select name = case when b.name like '%apple%' then 'apple' else b.name end ...and rest of your col.
– junketsu
Nov 19 '18 at 14:22
Select name = case when b.name like '%apple%' then 'apple' else b.name end ...and rest of your col.
– junketsu
Nov 19 '18 at 14:22
Have a look at your data. It doesn't make sense that there are two different names for the same account
– holder
Nov 19 '18 at 14:23
Have a look at your data. It doesn't make sense that there are two different names for the same account
– holder
Nov 19 '18 at 14:23
|
show 2 more comments
2 Answers
2
active
oldest
votes
Since you don't seem to care which name is chosen, just place an aggregate on that column.
It doesn't make sense that the same receiver ID can have different names though. Looks like you aren't using keys or constraints at all here...
SELECT
min(b.name),
a.receiver,
DATE1 = min(a.DATE1),
FROM Table1 a
Inner join Table2 b
on a.receiver = b.account
WHERE
Company in (1,2,3,4,5)
group by RECEIVER
answered Nov 19 '18 at 14:23
scsimon
20.6k41536
Thanks! this is perfect
– MishMish
Nov 19 '18 at 14:25
1
As good a guess as any until they clarify what they actually want.
– MatBailie
Nov 19 '18 at 14:28
add a comment |
You need to aggregate the name, do not group by it. For example, MIN(name) will show 'Appple' and MAX(name) will show 'Apple A/S'.
On other hand a.DATE1 = min(a.DATE1) is a wrong expression in the SELECT clause.
SELECT MIN(b.Name), MAX(a.receiver), min(a.DATE1) AS DATE1
FROM Table1 a
Inner join Table2 b
on a.receiver = b.account
WHERE
Company in (1,2,3,4,5)
group by RECEIVER
answered Nov 19 '18 at 14:28
serge
59537
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%2f53376461%2fsql-server-retrieve-1-value-from-a-duplicate%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Since you don't seem to care which name is chosen, just place an aggregate on that column.
It doesn't make sense that the same receiver ID can have different names though. Looks like you aren't using keys or constraints at all here...
SELECT
min(b.name),
a.receiver,
DATE1 = min(a.DATE1),
FROM Table1 a
Inner join Table2 b
on a.receiver = b.account
WHERE
Company in (1,2,3,4,5)
group by RECEIVER
answered Nov 19 '18 at 14:23
scsimon
20.6k41536
Thanks! this is perfect
– MishMish
Nov 19 '18 at 14:25
1
As good a guess as any until they clarify what they actually want.
– MatBailie
Nov 19 '18 at 14:28
add a comment |
Since you don't seem to care which name is chosen, just place an aggregate on that column.
It doesn't make sense that the same receiver ID can have different names though. Looks like you aren't using keys or constraints at all here...
SELECT
min(b.name),
a.receiver,
DATE1 = min(a.DATE1),
FROM Table1 a
Inner join Table2 b
on a.receiver = b.account
WHERE
Company in (1,2,3,4,5)
group by RECEIVER
answered Nov 19 '18 at 14:23
scsimon
20.6k41536
Thanks! this is perfect
– MishMish
Nov 19 '18 at 14:25
1
As good a guess as any until they clarify what they actually want.
– MatBailie
Nov 19 '18 at 14:28
add a comment |
Since you don't seem to care which name is chosen, just place an aggregate on that column.
It doesn't make sense that the same receiver ID can have different names though. Looks like you aren't using keys or constraints at all here...
SELECT
min(b.name),
a.receiver,
DATE1 = min(a.DATE1),
FROM Table1 a
Inner join Table2 b
on a.receiver = b.account
WHERE
Company in (1,2,3,4,5)
group by RECEIVER
answered Nov 19 '18 at 14:23
scsimon
20.6k41536
Since you don't seem to care which name is chosen, just place an aggregate on that column.
It doesn't make sense that the same receiver ID can have different names though. Looks like you aren't using keys or constraints at all here...
SELECT
min(b.name),
a.receiver,
DATE1 = min(a.DATE1),
FROM Table1 a
Inner join Table2 b
on a.receiver = b.account
WHERE
Company in (1,2,3,4,5)
group by RECEIVER
answered Nov 19 '18 at 14:23
scsimon
20.6k41536
answered Nov 19 '18 at 14:23
scsimon
20.6k41536
answered Nov 19 '18 at 14:23
scsimon
20.6k41536
answered Nov 19 '18 at 14:23
scsimon
20.6k41536
20.6k41536
Thanks! this is perfect
– MishMish
Nov 19 '18 at 14:25
1
As good a guess as any until they clarify what they actually want.
– MatBailie
Nov 19 '18 at 14:28
add a comment |
Thanks! this is perfect
– MishMish
Nov 19 '18 at 14:25
1
As good a guess as any until they clarify what they actually want.
– MatBailie
Nov 19 '18 at 14:28
Thanks! this is perfect
– MishMish
Nov 19 '18 at 14:25
Thanks! this is perfect
– MishMish
Nov 19 '18 at 14:25
1
1
As good a guess as any until they clarify what they actually want.
– MatBailie
Nov 19 '18 at 14:28
As good a guess as any until they clarify what they actually want.
– MatBailie
Nov 19 '18 at 14:28
add a comment |
You need to aggregate the name, do not group by it. For example, MIN(name) will show 'Appple' and MAX(name) will show 'Apple A/S'.
On other hand a.DATE1 = min(a.DATE1) is a wrong expression in the SELECT clause.
SELECT MIN(b.Name), MAX(a.receiver), min(a.DATE1) AS DATE1
FROM Table1 a
Inner join Table2 b
on a.receiver = b.account
WHERE
Company in (1,2,3,4,5)
group by RECEIVER
answered Nov 19 '18 at 14:28
serge
59537
add a comment |
You need to aggregate the name, do not group by it. For example, MIN(name) will show 'Appple' and MAX(name) will show 'Apple A/S'.
On other hand a.DATE1 = min(a.DATE1) is a wrong expression in the SELECT clause.
SELECT MIN(b.Name), MAX(a.receiver), min(a.DATE1) AS DATE1
FROM Table1 a
Inner join Table2 b
on a.receiver = b.account
WHERE
Company in (1,2,3,4,5)
group by RECEIVER
answered Nov 19 '18 at 14:28
serge
59537
add a comment |
You need to aggregate the name, do not group by it. For example, MIN(name) will show 'Appple' and MAX(name) will show 'Apple A/S'.
On other hand a.DATE1 = min(a.DATE1) is a wrong expression in the SELECT clause.
SELECT MIN(b.Name), MAX(a.receiver), min(a.DATE1) AS DATE1
FROM Table1 a
Inner join Table2 b
on a.receiver = b.account
WHERE
Company in (1,2,3,4,5)
group by RECEIVER
answered Nov 19 '18 at 14:28
serge
59537
You need to aggregate the name, do not group by it. For example, MIN(name) will show 'Appple' and MAX(name) will show 'Apple A/S'.
On other hand a.DATE1 = min(a.DATE1) is a wrong expression in the SELECT clause.
SELECT MIN(b.Name), MAX(a.receiver), min(a.DATE1) AS DATE1
FROM Table1 a
Inner join Table2 b
on a.receiver = b.account
WHERE
Company in (1,2,3,4,5)
group by RECEIVER
answered Nov 19 '18 at 14:28
serge
59537
answered Nov 19 '18 at 14:28
serge
59537
answered Nov 19 '18 at 14:28
serge
59537
answered Nov 19 '18 at 14:28
serge
59537
59537
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%2f53376461%2fsql-server-retrieve-1-value-from-a-duplicate%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
2
Is there any reason Apple and Apple A/S needs to be considered as same name?
– Santhana
Nov 19 '18 at 14:17
Yes, sometimes different users from the same company input different info when submitting for example a form.
– MishMish
Nov 19 '18 at 14:19
then that is data cleaning issue (long sustained way); for now you can handle easiest way via CASE statement
– junketsu
Nov 19 '18 at 14:21
Select name = case when b.name like '%apple%' then 'apple' else b.name end ...and rest of your col.
– junketsu
Nov 19 '18 at 14:22
Have a look at your data. It doesn't make sense that there are two different names for the same account
– holder
Nov 19 '18 at 14:23