SQL Count Total Number of Rows Whilst Using Limit

SQL Count total number of rows whilst using LIMIT

You need to offset your results for the given page you are on in addition to the separate query for getting the count that everyone else has mentioned.

$sql .= "posts p, images im, postimages pi WHERE
    i.active = 1 
    AND pi.post_id = p.id
    AND pi.image_id = im.image_id 
    ORDER BY created_at 
    LIMIT ". ($page - 1) * $pagesize .", ". $pagesize;

The normal warnings about SQL injection would apply here.

Get total number of rows while using limit clause

SQLite computes results on the fly when they are actually needed.

The only way to get the total count is to run the actual query (or better, SELECT COUNT(*)) without the LIMIT.

Run a query with a LIMIT/OFFSET and also get the total number of rows

Yes. With a simple window function:

SELECT *, count(*) OVER() AS full_count
FROM   tbl
WHERE  /* whatever */
ORDER  BY col1
OFFSET ?
LIMIT  ?

Be aware that the cost will be substantially higher than without the total number, but typically still cheaper than two separate queries. Postgres has to actually count all rows either way, which imposes a cost depending on the total number of qualifying rows. Details:

• Best way to get result count before LIMIT was applied

However, as Dani pointed out, when OFFSET is at least as great as the number of rows returned from the base query, no rows are returned. So we also don't get full_count.

If that's not acceptable, a possible workaround to always return the full count would be with a CTE and an OUTER JOIN:

WITH cte AS (
   SELECT *
   FROM   tbl
   WHERE  /* whatever */
   )
SELECT *
FROM  (
   TABLE  cte
   ORDER  BY col1
   LIMIT  ?
   OFFSET ?
   ) sub
RIGHT  JOIN (SELECT count(*) FROM cte) c(full_count) ON true;

You get one row of NULL values with the full_count appended if OFFSET is too big. Else, it's appended to every row like in the first query.

If a row with all NULL values is a possible valid result you have to check offset >= full_count to disambiguate the origin of the empty row.

This still executes the base query only once. But it adds more overhead to the query and only pays if that's less than repeating the base query for the count.

If indexes supporting the final sort order are available, it might pay to include the ORDER BY in the CTE (redundantly).

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

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.

How to count all rows when using SELECT with LIMIT in MySQL query?

What you are looking for is this

SELECT SQL_CALC_FOUND_ROWS A.ID, A.NAME, B.ID, B.NAME
FROM table1 A
JOIN table2 B ON ( A.ID = B.TABLE1_ID )
WHERE
  cond1, cond2, ..., condN
LIMIT 10

SELECT FOUND_ROWS();

Counting MySQL records with a LIMIT

Don't use COUNT(*) to count the number of rows (for a lot of reasons). Write out your full query, and add SQL_CALC_FOUND_ROWS right after SELECT:

SELECT SQL_CALC_FOUND_ROWS id, title FROM foo LIMIT 5;

Then, after that query executed (right after), run:

SELECT FOUND_ROWS();

That will return the number of rows the original SELECT would have returned if you didn't have the LIMIT on the end (accounting for all joins and where clauses).

It's not portable, but it's very efficient (and IMHO the right way of handling this type of problem).

Count total number of rows without the limit

OK, I hope I understand now what you're trying to do. I believe you have to "turn around" your select and use a CTE (Common Table Expression) instead of a subselect to achieve this.

Try this:

DECLARE @queryResult VARCHAR(MAX)

;WITH MyDerivedTable AS
(
    SELECT 
       items.id,
       items.title,
       ROW_NUMBER() OVER(ORDER BY date_added DESC) AS RowNum
    FROM dbo.cars
    INNER JOIN dbo.items ON items.id = cars.item_id
    WHERE rejected = 0 
) 
SELECT
   @queryResult = 
   (SELECT
       ID, Title,
       (SELECT MAX(RowNum) FROM MyDerivedTable) AS 'Count'
    FROM
       MyDerivedTable
    WHERE
        RowNum BETWEEN (@page-1)*2+1 AND (@page*2) 
    FOR XML PATH('car'),ROOT('items')
   )

SELECT @queryResult

That should output your ID, Title and the Count (which is the max of the RowNum) for each car entry.

MySQL COUNT with LIMIT

This is actually how your query works and is a normal behaviour. Using LIMIT you will not limit the count or sum but only the returned rows. So your query will return n rows as stated in your LIMIT clause. And since your query actually returns only one row, applying a (non-zero) limit has no effect on the results.

However, your second query will work as expected and is an established way of solving this problem.

TSQL: Is there a way to limit the rows returned and count the total that would have been returned without the limit (without adding it to every row)?

As @MartinSmith mentioned in a comment on this question, there is no direct (i.e. pure T-SQL) way of getting the total numbers of rows that would be returned while at the same time limiting it. In the past I have done the method of:

• dump the query to a temp table to grab @@ROWCOUNT (the total set)
• use ROW_NUBMER() AS [ResultID] on the ordered results of the main query
• SELECT TOP (n) FROM #Temp ORDER BY [ResultID] or something similar

Of course, the downside here is that you have the disk I/O cost of getting those records into the temp table. Put [tempdb] on SSD? :)

I have also experienced the "run COUNT(*) with the same rest of the query first, then run the regular SELECT" method (as advocated by @Blam), and it is not a "free" re-run of the query:

• It is a full re-run in many cases. The issue is that when doing COUNT(*) (hence not returning any fields), the optimizer only needs to worry about indexes in terms of the JOIN, WHERE, GROUP BY, ORDER BY clauses. But when you want some actual data back, that could change the execution plan quite a bit, especially if the indexes used to get the COUNT(*) are not "covering" for the fields in the SELECT list.
• The other issue is that even if the indexes are all the same and hence all of the data pages are still in cache, that just saves you from the physical reads. But you still have the logical reads.

I'm not saying this method doesn't work, but I think the method in the Question that only does the COUNT(*) conditionally is far less stressful on the system.

The method advocated by @Gordon is actually functionally very similar to the temp table method I described above: it dumps the full result set to [tempdb] (the INSERTED table is in [tempdb]) to get the full @@ROWCOUNT and then it gets a subset. On the downside, the INSTEAD OF TRIGGER method is:

• a lot more work to set up (as in 10x - 20x more): you need a real table to represent each distinct result set, you need a trigger, the trigger needs to either be built dynamically, or get the number of rows to return from some config table, or I suppose it could get it from CONTEXT_INFO() or a temp table. Still, the whole process is quite a few steps and convoluted.

• very inefficient: first it does the same amount of work dumping the full result set to a table (i.e. into the INSERTED table--which lives in [tempdb]) but then it does an additional step of selecting the desired subset of records (not really a problem as this should still be in the buffer pool) to go back into the real table. What's worse is that second step is actually double I/O as the operation is also represented in the transaction log for the database where that real table exists. But wait, there's more: what about the next run of the query? You need to clear out this real table. Whether via DELETE or TRUNCATE TABLE, it is another operation that shows up (the amount of representation based on which of those two operations is used) in the transaction log, plus is additional time spent on the additional operation. AND, let's not forget about the step that selects the subset out of INSERTED into the real table: it doesn't have the opportunity to use an index since you can't index the INSERTED and DELETED tables. Not that you always would want to add an index to the temp table, but sometimes it helps (depending on the situation) and you at least have that choice.

• overly complicated: what happens when two processes need to run the query at the same time? If they are sharing the same real table to dump into and then select out of for the final output, then there needs to be another column added to distinguish between the SPIDs. It could be @@SPID. Or it could be a GUID created before the initial INSERT into the real table is called (so that it can be passed to the INSTEAD OF trigger via CONTEXT_INFO() or a temp table). Whatever the value is, it would then be used to do the DELETE operation once the final output has been selected. And if not obvious, this part influences a performance issue brought up in the prior bullet: TRUNCATE TABLE cannot be used as it clears the entire table, leaving DELETE FROM dbo.RealTable WHERE ProcessID = @WhateverID; as the only option.

  Now, to be fair, it is possible to do the final SELECT from within the trigger itself. This would reduce some of the inefficiency as the data never makes it into the real table and then also never needs to be deleted. It also reduces the over-complication as there should be no need to separate the data by SPID. However, this is a very time-limited solution as the ability to return results from within a trigger is going bye-bye in the next release of SQL Server, so sayeth the MSDN page for the disallow results from triggers Server Configuration Option:

  This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. We recommend that you set this value to 1.

The only actual way to do:

• the query one time
• get a subset of rows
• and still get the total row count of the full result set

is to use .Net. If the procs are being called from app code, please see "EDIT 2" at the bottom. If you want to be able to randomly run various stored procedures via ad hoc queries, then it would have to be a SQLCLR stored procedure so that it could be generic and work for any query as stored procedures can return dynamic result sets and functions cannot. The proc would need at least 3 parameters:

• @QueryToExec NVARCHAR(MAX)
• @RowsToReturn INT
• @TotalRows INT OUTPUT

The idea is to use "Context Connection = true;" to make use of the internal / in-process connection. You then do these basic steps:

1. call ExecuteDataReader()
2. before you read any rows, do a GetSchemaTable()
3. from the SchemaTable you get the result set field names and datatypes
4. from the result set structure you construct a SqlDataRecord
5. with that SqlDataRecord you call SqlContext.Pipe.SendResultsStart(_DataRecord)
6. now you start calling Reader.Read()
7. for each row you call:
   
   1. Reader.GetValues()
   2. DataRecord.SetValues()
   3. SqlContext.Pipe.SendResultRow(_DataRecord)
   4. RowCounter++
8. Rather than doing the typical "while (Reader.Read())", you instead include the @RowsToReturn param: while(Reader.Read() && RowCounter < RowsToReturn.Value)
9. After that while loop, call SqlContext.Pipe.SendResultsEnd() to close the result set (the one that you are sending, not the one you are reading)
10. then do a second while loop that cycles through the rest of the result, but never gets any of the fields:
    while (Reader.Read())
    {
    RowCounter++;
    }
11. then just set TotalRows = RowCounter; which will pass back the number of rows for the full result set, even though you only returned the top n rows of it :)

Not sure how this performs against the temp table method, the dual call method, or even @M.Ali's method (which I have also tried and kinda like, but the question was specific to not sending the value as a column), but it should be fine and does accomplish the task as requested.

EDIT:
Even better! Another option (a variation on the above C# suggestion) is to use the @@ROWCOUNT from the T-SQL stored procedure, sent as an OUTPUT parameter, rather than cycling through the rest of the rows in the SqlDataReader. So the stored procedure would be similar to:

CREATE PROCEDURE SchemaName.ProcName
(
   @Param1 INT,
   @Param2 VARCHAR(05),
   @RowCount INT OUTPUT = -1 -- default so it doesn't have to be passed in
)
AS
SET NOCOUNT ON;

{any ol' query}

SET @RowCount = @@ROWCOUNT;

Then, in the app code, create a new SqlParameter, Direction = Output, for "@RowCount". The numbered steps above stay the same, except the last two (10 and 11), which change to:

10. Instead of the 2nd while loop, just call Reader.Close()
11. Instead of using the RowCounter variable, set TotalRows = (int)RowCountOutputParam.Value;

I have tried this and it does work. But so far I have not had time to test the performance against the other methods.

EDIT 2:
If the T-SQL stored procs are being called from the app layer (i.e. no need for ad hoc execution) then this is actually a much simpler variation of the above C# methods. In this case you don't need to worry about the SqlDataRecord or the SqlContext.Pipe methods. Assuming you already have a SqlDataReader set up to pull back the results, you just need to:

1. Make sure the T-SQL stored proc has a @RowCount INT OUTPUT = -1 parameter
2. Make sure to SET @RowCount = @@ROWCOUNT; immediately after the query
3. Register the OUTPUT param as a SqlParameter having Direction = Output
4. Use a loop similar to: while(Reader.Read() && RowCounter < RowsToReturn) so that you can stop retrieving results once you have pulled back the desired amount.
5. Remember to not limit the result in the stored proc (i.e. no TOP (n))

At that point, just like what was mentioned in the first "EDIT" above, just close the SqlDataReader and grab the .Value of the OUTPUT param :).

---

Related Topics