Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to get the number of total results when there is LIMIT in query?

Tags:

sql

mysql

I have a query like this:

select * from table where id <= 10 limit 5;  // table has +10 rows

The number of result in the above query ^ is 10 rows. Now I want to know, how can I get the number of total result in this query:

select * from table where col = 'anything' limit 5;

How to calculate the number of all results (regardless of limit) in this ^ ?

Actually I want this number:

select count(*) as total_number from table where col = 'anything'

Now I want to know how can I get the number of total results without another query.

like image 270
Shafizadeh Avatar asked Nov 24 '15 09:11

Shafizadeh


People also ask

How can you limit the number of results returned by a query?

The SQL SELECT LIMIT statement is used to retrieve records from one or more tables in a database and limit the number of records returned based on a limit value. TIP: SELECT LIMIT is not supported in all SQL databases. For databases such as SQL Server or MSAccess, use the SELECT TOP statement to limit your results.

What SQL clause limits the number of results?

The LIMIT clause is used to specify the number of records to return. The LIMIT clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.

How can I get Limit Records in SQL?

SQL SELECT TOP, LIMIT and ROWNUM KeywordsThe LIMIT , SELECT TOP or ROWNUM command is used to specify the number of records to return. Note: SQL Server uses SELECT TOP . MySQL uses LIMIT , and Oracle uses ROWNUM .

How do I limit the number of results in MySQL?

The LIMIT clause is used in the SELECT statement to constrain the number of rows to return. The LIMIT clause accepts one or two arguments. The values of both arguments must be zero or positive integers.


2 Answers

Add a column, total, for example:

select t.*
     , (select count(*) from tbl where col = t.col) as total
from tbl t
where t.col = 'anything'
limit 5

As stated by @Tim Biegeleisen: limit keyword is applied after everything else, so the count(*) still returns the right answer.

like image 191
potashin Avatar answered Nov 15 '22 20:11

potashin


You need the SQL_CALC_FOUND_ROWS option in your query and FOUND_ROWS() function to do this:

DECLARE @rows int
SELECT SQL_CALC_FOUND_ROWS * from table where col = 'anything' limit 5;

SET @rows = FOUND_ROWS(); --for a later use
like image 24
Thanos Markou Avatar answered Nov 15 '22 22:11

Thanos Markou