Why limit is not working on this sql query

[darksystem]

Hi, I was wondering why limit is not working on this mysql query

select count(id) as total from wp_posts where post_status='publish' limit 10;

I got more than a thousand records and I want to limit the returned result into 10 records only but still does return more than a thousand records counts.

Any help is highly appreciated.

[metho]

It will only return 1 result, total = x, due to the use of count(). Limit does not apply when count() is used in this manner. This query is only asking for the total number of published posts, nothing else.

If you’re expecting more than one row, then it’s the wrong query. Why don’t you explain what data you want returned in the result…

[r937]

[quote=“metho, post:2, topic:103491, full:true”] Limit does not apply when count() is used in this manner. [/quote]actually, it does

[metho]

Care to elucidate? Since only the aggregate is included in the select statement, only 1 row is ever going to return.

[Mittineague]

EDIT

The way I see it working (not tested) is there will be one row returned with the value “10”

A pointless query if there are 10 or more fields meeting the WHERE, but until darksystem returns to clarify what is really wanted here, I don’t have any advice.

:d’oh: see next reply

[r937]

[quote=“metho, post:4, topic:103491, full:true”]
Care to elucidate? [/quote]sure

the query will, as you explained, return only one row

LIMIT checks that 1 < 10 and allows the row to be returned

then the query stops

[r937]

[quote=“Mittineague, post:5, topic:103491, full:true”]…one row returned with the value “10”

A pointless query if there are 10 or more fields meeting the WHERE…
[/quote]
why pointless?

note LIMIT restricts the number of rows returned, not inspected

and since this is an aggregate query, LIMIT applies to the number of aggregate rows returned

which will be 1

[metho]

Hate to be pedantic, but using limit on a query than can only return one row is redundant, serves no purpose, pointless, does not apply. Could you explain the value of limit that you interpret in the OPs query?

[darksystem]

Say my records has more than a thousand records.

I want to count all the rows but limits to 10 only. and that’s why my sql query is

select count(id) as total from wp_posts where post_status=‘publish’ limit 10;

however the result will all return the number of counted rows which is more than a thousand instead of 10 only as my declared limit.

So pls help how to limit the counted rows?

Note: i used count to count the number of rows not to display the values.

[metho]

Your explanation isn’t very clear and is full of contradictions. Please forget the query and tell me what data you want and how it will be used in the web page.

[r937]

[quote=“metho, post:8, topic:103491, full:true”]Could you explain the value of limit that you interpret in the OPs query?
[/quote]sure, i’d be happy to give you my interpretation

it’s completely useless

[r937]

[quote=“darksystem, post:9, topic:103491, full:true”]
So pls help how to limit the counted rows?[/quote]

well, you could do this –SELECT LEAST(COUNT(id),10) AS total FROM wp_posts WHERE post_status='publish'but i don’t see how this would be a useful number to show

[Mittineague]

It looks like the query is an attempt to get two different things.
IMHO it should get either the count() if the number of rows is wanted
or, 10 rows based on WHERE criteria
@darksystem Sorry, but I don’t understand which it is you’re after.