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?
GROUP BY problem
Moderator: General Moderators
yes it willacidHL wrote:I don't think it will work if you're actually selecting any fields at the same time.
try this
Code: Select all
SELECT `member`, COUNT(*) FROM `table` GROUP BY `member` ORDER BY `id` DESC LIMIT 10But whats the point of COUNT(*)? your asking for 10, and that will return 10 each time, wont it? ...Pimptastic wrote:yes it willacidHL wrote:I don't think it will work if you're actually selecting any fields at the same time.
try this
Is you table actually called "table" becuase that is a reserved wordCode: Select all
SELECT `member`, COUNT(*) FROM `table` GROUP BY `member` ORDER BY `id` DESC LIMIT 10
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
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