4

In mysql query, I have something like I select and order, and use where clause, etc..., and finally I have limit 10 for example. Something like this: 

Select *
From employee
where fired=0
limit 10

The problem is, how do I know if limit was used and the result was truncated? Basically I want to know easily if there were more results that got cut off from limit. I don't want to do something like 

Select (count *)
From employee
where fired=0

subtract

Select (count *)
From employee
where fired=0
limit 10

Is there a simple way for this? Perhaps, like you know how after you run an insert statement, you can get the id using php built in function get_sql_id() (something like that), is there a sql_count_without_limit()?

Thanks

omega
  • 40,311
  • 81
  • 251
  • 474

3 Answers3

7

You could do 

Select *
From employee
where fired=0
limit 10+1

So that:

0-10 values returned: no limit was reached
11 values returned: Limit was reached

And then only use the first 10 values that are returned.

update Raymond's answer is waaaaay better than mine!

Peter M
  • 7,309
  • 3
  • 50
  • 91
  • Throws this error: "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+1'". – Avatar Mar 26 '20 at 06:34
2

You can use the found_rows function like this:

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
-> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();

For more information see mysql reference

Raymond
  • 3,630
  • 2
  • 19
  • 22
  • This function is deprecated. They suggest to use two seperate queries. One with `LIMIT` an one without `LIMIT` https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_found-rows – Murolack Sep 18 '19 at 14:27
2

You can return the remaining rows as an column like below

SET @limit = 10;

SELECT *,
(SELECT count(*) from employee)- @limit as remaining
From employee
where fired=0
limit 10
A.B
  • 20,110
  • 3
  • 37
  • 71
  • Doesn't count(*) become at most 10? – omega Mar 11 '15 at 19:42
  • no if total rows are 30 and you apply limit=10 the remaining will be 30-10 that is 20 – A.B Mar 11 '15 at 19:43
  • This is also a cool answer, maybe better than Raymonds because I do one less php mysql query. – omega Mar 11 '15 at 19:44
  • Actually, if I use this, then I would need to make two queries, one to get the results, and another one with mostly same code that is used just to get `remaining`. Is there a way to combine them? If not then I think Raymonds answer is more efficient. – omega Mar 11 '15 at 19:52
  • @omega see if it works correctly for you in single query :) – A.B Mar 11 '15 at 20:02
  • I tried putting like `,count(*) as total` on my initial query which actually selects columns, but it gave an error about cannot count without group or something like that. – omega Mar 11 '15 at 20:15