Page 1 of 1
GROUP BY problem
Posted: Wed May 10, 2006 6:39 am
by Ozz
I want to take last 10 voted members by excluding same members.
Table is like this:
id / member / vote
1 / 10 / 1
2 / 15 / 0
3 / 32 / 0
4 / 10 / 0
I think this query is ok but not working:
SELECT member, COUNT(*) FROM table GROUP BY member ORDER BY id DESC LIMIT 10
What do you think?
Posted: Wed May 10, 2006 6:44 am
by acidHL
Why have you got COUNT(*) in there?
I don't think it will work if you're actually selecting any fields at the same time.
Posted: Wed May 10, 2006 6:59 am
by JayBird
acidHL wrote:I don't think it will work if you're actually selecting any fields at the same time.
yes it will
try this
Code: Select all
SELECT `member`, COUNT(*) FROM `table` GROUP BY `member` ORDER BY `id` DESC LIMIT 10
Is you table actually called "table" becuase that is a reserved word
Posted: Wed May 10, 2006 7:12 am
by $phpNut
Pimptastic wrote:acidHL wrote:I don't think it will work if you're actually selecting any fields at the same time.
yes it will
try this
Code: Select all
SELECT `member`, COUNT(*) FROM `table` GROUP BY `member` ORDER BY `id` DESC LIMIT 10
Is you table actually called "table" becuase that is a reserved word
But whats the point of COUNT(*)? your asking for 10, and that will return 10 each time, wont it? ...
Posted: Wed May 10, 2006 9:34 am
by GM
When COUNT(*) is used with GROUP BY it counts the records returned for each group:
Code: Select all
mysql> SELECT * FROM COUNTTEST
-> ;
+-------+--------+
| UNAME | SOMMAT |
+-------+--------+
| Dave | 1 |
| Dave | 5 |
| Dave | 8 |
| Bill | 2 |
| Bill | 10 |
| Dan | 1 |
+-------+--------+
6 rows in set (0.01 sec)
mysql> SELECT UNAME, COUNT(*) FROM COUNTTEST GROUP BY UNAME;
+-------+----------+
| UNAME | COUNT(*) |
+-------+----------+
| Bill | 2 |
| Dan | 1 |
| Dave | 3 |
+-------+----------+
3 rows in set (0.44 sec)
SOLUTION
Posted: Wed May 10, 2006 12:24 pm
by Ozz
I found the solution from another board. Here is the query maybe helps somebody else too:
Code: Select all
select member
, max(id) as last_id
from daTable
group
by member
order
by last_id desc limit 10
Posted: Wed May 10, 2006 4:28 pm
by pickle
Moved to 'Databases' forum.
Posted: Wed May 10, 2006 4:41 pm
by $phpNut
GM wrote:When COUNT(*) is used with GROUP BY it counts the records returned for each group
Ah i see, learn something new every day ...