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.

edited Jan 1 at 12:00

Brian Tompsett - 汤莱恩

4,2321338102

asked Jan 1 at 11:51

CheapGame Host

add a comment |

I searched around online, and I couldn't find anything about this. If anyone could help me with this, I would be grateful.

edited Jan 1 at 12:00

Brian Tompsett - 汤莱恩

4,2321338102

asked Jan 1 at 11:51

CheapGame Host

add a comment |

I searched around online, and I couldn't find anything about this. If anyone could help me with this, I would be grateful.

edited Jan 1 at 12:00

Brian Tompsett - 汤莱恩

4,2321338102

asked Jan 1 at 11:51

CheapGame Host

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 Host

edited Jan 1 at 12:00

Brian Tompsett - 汤莱恩

4,2321338102

asked Jan 1 at 11:51

CheapGame Host

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

asked Jan 1 at 11:51

CheapGame Host

asked Jan 1 at 11:51

CheapGame Host

asked Jan 1 at 11:51

CheapGame Host

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 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_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
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

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

Name

Required, but never shown

2 Answers
2

active

oldest

votes

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 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 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 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 Tuhin

16k21033

answered Jan 1 at 11:53

Zaynul Abadin Tuhin

16k21033

answered Jan 1 at 11:53

Zaynul Abadin Tuhin

16k21033

answered Jan 1 at 11:53

Zaynul 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_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_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_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_84

510412

answered Jan 1 at 12:16

Ayan_84

510412

answered Jan 1 at 12:16

Ayan_84

510412

answered Jan 1 at 12:16

Ayan_84

510412

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');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu