CODEWITHSUNDEEP
mysql
user3307783
user3307783

Reputation: 157

Limit count query from column

I have query which is going to count which name has some number in field and count how many times is repeats. The result is name with the most count number.

I need to limit this count to 10 or 15 rows now to count, but when i put limit it doesn't work.

Select home, count(*) as count
from (select * from results where more='33' and name='FirstName' LIMIT 10) as limit10
group by home
order by count(more) desc
LIMIT 1;

I try this with LEAST but its same. I add now order by date, to count exec last rows, but its same...

Select home, LEAST( count(*), 12) as count
from (SELECT *,STR_TO_DATE(date, '%d.%m.%Y') as datum FROM results where more='33' and name='FirstName' ORDER BY datum desc) as limit12
group by home
order by count(more) desc
LIMIT 1;

Upvotes: 1

Views: 119

Answers (1)

Tudor Constantin
Tudor Constantin

Reputation: 26861

I suggest you to use the LEAST function:

Select home, LEAST( count(*), 10 ) as count
from results where more='33' and name='FirstName'
group by home
order by count(more) desc
LIMIT 1;

This would get you the same end result and as an added bonus you get rid of that subquery

Upvotes: 1

Related Questions