Mixing
SQL query to get all unique rows with uid
I want to see which user has received the most highfives using a SQL query. My table looks like following, id | uid | ip. Now, I want to count the amount of rows a uid has, but it has to be unique with the ip. So nobody can give multiple highfives to a person.
I searched around online, and I couldn't find anything about this. If anyone could help me with this, I would be grateful.
sql phpmyadmin
edited Jan 1 at 12:00
Brian Tompsett - 汤莱恩
4,2321338102
asked Jan 1 at 11:51
CheapGame HostCheapGame Host
64
add a comment |
I want to see which user has received the most highfives using a SQL query. My table looks like following, id | uid | ip. Now, I want to count the amount of rows a uid has, but it has to be unique with the ip. So nobody can give multiple highfives to a person.
I searched around online, and I couldn't find anything about this. If anyone could help me with this, I would be grateful.
sql phpmyadmin
edited Jan 1 at 12:00
Brian Tompsett - 汤莱恩
4,2321338102
asked Jan 1 at 11:51
CheapGame HostCheapGame Host
64
add a comment |
I want to see which user has received the most highfives using a SQL query. My table looks like following, id | uid | ip. Now, I want to count the amount of rows a uid has, but it has to be unique with the ip. So nobody can give multiple highfives to a person.
I searched around online, and I couldn't find anything about this. If anyone could help me with this, I would be grateful.
sql phpmyadmin
edited Jan 1 at 12:00
Brian Tompsett - 汤莱恩
4,2321338102
asked Jan 1 at 11:51
CheapGame HostCheapGame Host
64
I want to see which user has received the most highfives using a SQL query. My table looks like following, id | uid | ip. Now, I want to count the amount of rows a uid has, but it has to be unique with the ip. So nobody can give multiple highfives to a person.
I searched around online, and I couldn't find anything about this. If anyone could help me with this, I would be grateful.
sql phpmyadmin
sql phpmyadmin
edited Jan 1 at 12:00
Brian Tompsett - 汤莱恩
4,2321338102
asked Jan 1 at 11:51
CheapGame HostCheapGame Host
64
edited Jan 1 at 12:00
Brian Tompsett - 汤莱恩
4,2321338102
asked Jan 1 at 11:51
CheapGame HostCheapGame Host
64
edited Jan 1 at 12:00
Brian Tompsett - 汤莱恩
4,2321338102
edited Jan 1 at 12:00
Brian Tompsett - 汤莱恩
4,2321338102
edited Jan 1 at 12:00
Brian Tompsett - 汤莱恩
4,2321338102
4,2321338102
asked Jan 1 at 11:51
CheapGame HostCheapGame Host
64
asked Jan 1 at 11:51
CheapGame HostCheapGame Host
64
asked Jan 1 at 11:51
CheapGame HostCheapGame Host
64
64
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
you can try like below
select ip, count(distinct uid) from table t
group by ip
answered Jan 1 at 11:53
Zaynul Abadin TuhinZaynul Abadin Tuhin
16k21033
add a comment |
SELECT uid,COUNT(ip)NoOfVotes FROM
(SELECT uid,ip,Serial=ROW_NUMBER() OVER(PARTITION BY ip,uid ORDER BY uid) FROM
dbo.tbl_user)A
WHERE Serial=1
GROUP BY uid
I think this will give you perfect vote counting. Using Row Number actively remove duplicates from same ip,same uid. Voting to multiple uid from same ip is allowed in this query.
answered Jan 1 at 12:16
Ayan_84Ayan_84
510412
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%2f53995185%2fsql-query-to-get-all-unique-rows-with-uid%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
you can try like below
select ip, count(distinct uid) from table t
group by ip
answered Jan 1 at 11:53
Zaynul Abadin TuhinZaynul Abadin Tuhin
16k21033
add a comment |
you can try like below
select ip, count(distinct uid) from table t
group by ip
answered Jan 1 at 11:53
Zaynul Abadin TuhinZaynul Abadin Tuhin
16k21033
add a comment |
you can try like below
select ip, count(distinct uid) from table t
group by ip
answered Jan 1 at 11:53
Zaynul Abadin TuhinZaynul Abadin Tuhin
16k21033
you can try like below
select ip, count(distinct uid) from table t
group by ip
answered Jan 1 at 11:53
Zaynul Abadin TuhinZaynul Abadin Tuhin
16k21033
answered Jan 1 at 11:53
Zaynul Abadin TuhinZaynul Abadin Tuhin
16k21033
answered Jan 1 at 11:53
Zaynul Abadin TuhinZaynul Abadin Tuhin
16k21033
answered Jan 1 at 11:53
Zaynul Abadin TuhinZaynul Abadin Tuhin
16k21033
16k21033
add a comment |
add a comment |
SELECT uid,COUNT(ip)NoOfVotes FROM
(SELECT uid,ip,Serial=ROW_NUMBER() OVER(PARTITION BY ip,uid ORDER BY uid) FROM
dbo.tbl_user)A
WHERE Serial=1
GROUP BY uid
I think this will give you perfect vote counting. Using Row Number actively remove duplicates from same ip,same uid. Voting to multiple uid from same ip is allowed in this query.
answered Jan 1 at 12:16
Ayan_84Ayan_84
510412
add a comment |
SELECT uid,COUNT(ip)NoOfVotes FROM
(SELECT uid,ip,Serial=ROW_NUMBER() OVER(PARTITION BY ip,uid ORDER BY uid) FROM
dbo.tbl_user)A
WHERE Serial=1
GROUP BY uid
I think this will give you perfect vote counting. Using Row Number actively remove duplicates from same ip,same uid. Voting to multiple uid from same ip is allowed in this query.
answered Jan 1 at 12:16
Ayan_84Ayan_84
510412
add a comment |
SELECT uid,COUNT(ip)NoOfVotes FROM
(SELECT uid,ip,Serial=ROW_NUMBER() OVER(PARTITION BY ip,uid ORDER BY uid) FROM
dbo.tbl_user)A
WHERE Serial=1
GROUP BY uid
I think this will give you perfect vote counting. Using Row Number actively remove duplicates from same ip,same uid. Voting to multiple uid from same ip is allowed in this query.
answered Jan 1 at 12:16
Ayan_84Ayan_84
510412
SELECT uid,COUNT(ip)NoOfVotes FROM
(SELECT uid,ip,Serial=ROW_NUMBER() OVER(PARTITION BY ip,uid ORDER BY uid) FROM
dbo.tbl_user)A
WHERE Serial=1
GROUP BY uid
I think this will give you perfect vote counting. Using Row Number actively remove duplicates from same ip,same uid. Voting to multiple uid from same ip is allowed in this query.
answered Jan 1 at 12:16
Ayan_84Ayan_84
510412
answered Jan 1 at 12:16
Ayan_84Ayan_84
510412
answered Jan 1 at 12:16
Ayan_84Ayan_84
510412
answered Jan 1 at 12:16
Ayan_84Ayan_84
510412
510412
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%2f53995185%2fsql-query-to-get-all-unique-rows-with-uid%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
