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