GROUP BY problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Ozz
Forum Commoner
Posts: 26
Joined: Wed Aug 21, 2002 11:47 pm

GROUP BY problem

Post 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?
acidHL
Forum Commoner
Posts: 41
Joined: Wed Dec 07, 2005 7:38 am

Post 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.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
User avatar
$phpNut
Forum Commoner
Posts: 40
Joined: Tue May 09, 2006 5:13 pm

Post 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? ...
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post 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)
Ozz
Forum Commoner
Posts: 26
Joined: Wed Aug 21, 2002 11:47 pm

SOLUTION

Post 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
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Moved to 'Databases' forum.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
$phpNut
Forum Commoner
Posts: 40
Joined: Tue May 09, 2006 5:13 pm

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