Use distinct with top 10

Question

I want to select the users from a database called log_buyers. log_buyers is a log with all items i sold to users. I use DISTINCT on field USERID to collect only the buyers one time (some people buy more items). I use it like this:

$lsel_log = mysql_query("select DISTINCT userid from log_buyers order by id");
while ($log = mysql_fetch_array($lsel_log)) {

Now i want to have the 10 most common users and see how much each user in the top paid. In the same table (log_buyers).

  $lsel_log = mysql_query("select DISTINCT userid from log_buyers order by id");
  while ($log = mysql_fetch_array($lsel_log)) {  

$lsel_total_5 = mysql_query("select * from log_buyers where userid = '$log[userid]' order by id desc");
 $total = mysql_num_rows($lsel_total_5);
 $total = $total * 5; 
echo "$log[userid] - $total";

The code works fine, except he is doing it for all the users who bought. I only want to do it for the top 10 users. (Most common in log_buyers).

@BernardoLima `TOP` doesn't work in MySQL, that's SQL Server or Oracle. — Niels Keurentjes, Aug 26 '14 at 15:27
possible duplicate of [Select most common value from a field in MySQL](http://stackoverflow.com/questions/7693613/select-most-common-value-from-a-field-in-mysql) — Robert, Aug 26 '14 at 15:32

score 0 · Answer 1 · answered Aug 26 '14 at 15:25

0

in the first query, add LIMIT 10 at the end of the query

answered Aug 26 '14 at 15:25

RichardBernards

3,146
1
22
30

That don't give me the most common 10 users in log_buyers – Banzouzi Aug 26 '14 at 15:28

score 0 · Answer 2 · edited May 23 '17 at 10:33

0

It's very easy question you should add LIMIT 10 to your query.

Edit: I think you will find your answer there

edited May 23 '17 at 10:33

Community

1
1

answered Aug 26 '14 at 15:26

Robert

19,800
5
55
85

That don't give me the most common 10 users in log_buyers – Banzouzi Aug 26 '14 at 15:28
so maybe you need this http://stackoverflow.com/questions/7693613/select-most-common-value-from-a-field-in-mysql – Robert Aug 26 '14 at 15:29
@Banzouzi I just updated my answer... I edited at the same time as your comment Robert, sorry for that – RichardBernards Aug 26 '14 at 15:32

score 0 · Accepted Answer · answered Aug 26 '14 at 15:30

0

Using GROUP BY with COUNT should get you the most common userids in that table.

SELECT userid FROM log_buyers 
GROUP BY userid 
ORDER BY COUNT(userid) DESC 
LIMIT 10

answered Aug 26 '14 at 15:30

Don't Panic

41,125
10
61
80

Use distinct with top 10

3 Answers3