COUNT, GROUP BY and LIMIT problem

W__Zwiers

Hope someone can help me with the following;

i try to execute the following MySql statement:

SELECT name, COUNT(*) FROM $table GROUP BY name LIMIT 50,50

but this doesn't seem to work.
SELECT name, COUNT(*) FROM $table GROUP BY name
works fine.

How can i fix this?

what i am trying to do is a 'count and group by' on a portion of the table.

Thanks in advance
W. Zwiers.

tomhath

What do you mean by "doesn't seem to work"?

I assume the query without the LIMIT returns more than 50 rows. The LIMIT query should examine all rows in the table and report the counts for Names 51 through 100 (it limits the output, not the input).

W__Zwiers

With doesn't work i mean that it returns nu result.

i have a table with 500 rows

SELECT name, COUNT(*) FROM $table GROUP BY name LIMIT 50, 50

returns 0 rows, i quess MySql executes te first part of the query and limits the result of that query from row 50 to 100

I'll try to explain what i want to do a little better:

I want to be able to do a query on a part of a table. So, for example a query on table row 100 to 200

something like this:
with tablerow (50 to 100) do (SELECT name, COUNT(*) FROM $table GROUP BY name)

Any help is much appreciated,
W. Zwiers

Traduim

If you try, you'll see you CANNOT use LIMIT when using GROUP BY.

You shall look for another system :-(

W__Zwiers

Seems to be so,

any other solutions are welcome, i'll keep digging,

W. Zwiers

swtz

dude,

Let us know why u need such query, and maybe we can find a workaround for it.

regards,

W__Zwiers

the database table is HUGE and i only need a COUNT and GROUP of the last... say 500 rows

So, any tips are welcome

OhLordy

Do you have an auto_increment unique_id?
If you do you can do
SELECT name, COUNT(*) FROM $table WHERE unique_id>(SELECT MAX(unique_id) FROM $table)-500

I think that should work.
Good Luck
Rob

W__Zwiers

Thanks for the tip, i'll try it out

mpauley73

Originally posted by W. Zwiers
Hope someone can help me with the following;

i try to execute the following MySql statement:

SELECT name, COUNT(*) FROM $table GROUP BY name LIMIT 50,50

but this doesn't seem to work.
SELECT name, COUNT(*) FROM $table GROUP BY name
works fine.

How can i fix this?

what i am trying to do is a 'count and group by' on a portion of the table.

Thanks in advance
W. Zwiers.

I had sort of the same issue today... Here is the SELECT statment I ended up with:

select distinct title, count(*) from ShowShare group by title ASC LIMIT 2 , 100000000

Mickey