CODEWITHSUNDEEP
mysqlsql-servert-sql
user1288792
user1288792

Reputation: 71

Is there an equivalent of SQL_CALC_FOUND_ROWS in SQL Server?

I have this mssql query:

with RESULT as(select  TITLE, URL, ROW_NUMBER() over (order by URL) as SeqValue from WEBSITE 
select * from RESULT where SeqValue>=20 and SeqValue<=40

I'd like to know how many record this query would return if the where statement was not there. I try with select count(*) from RESULT and try with @@ROWCOUNT and many others way but did not work. I need TITLE and URL from select, and in the end i need total record for the select.

For example in mysql query i have prepareStatement using SQL_CALC_FOUND_ROWS:

select SQL_CALC_FOUND_ROWS TITLE, URL from WEBSITE limit ?, ?

and after this select i have:

select FOUND_ROWS()

In this example returned value is total record for the mysql query. Total record is same with LIMIT and without LIMIT directive. I convert database from mysql to mssql and i have problem with this. Please help me...

Upvotes: 6

Views: 8757

Answers (4)

balakrishnan
balakrishnan

Reputation: 383

I know it is too late, but can be helpful with pagination and limits.

WITH paging AS (SELECT ROW_NUMBER() OVER(ORDER BY {$sortField} {$sortOrder}) as rowId, count(1) over() as ROW_COUNT, key FROM {$table})
SELECT * FROM {$table} result INNER JOIN paging ON result.key = paging.key WHERE rowId BETWEEN {$start} and {$end}

It has the property for sorting field, sort order and key is used to join the original table. The start and end are the values of your pagination.

Now you have total number of records on ROW_COUNT field with required fields. It also starts from 1 and not from 0.

Upvotes: 0

ghani
ghani

Reputation: 141

I had the same concern when trying to move from MySQL to SQL SERVER the solution is to inject this in your query:

COUNT (*) OVER () AS ROW_COUNT

ROWCOUNT appear in all rows in the result then it only remains for you to retrieve ROW_COUNT from the first row result (if exists) and not forget to reset the result pointer et Voila;-).

for example:

select COUNT (*) OVER () AS ROW_COUNT, URL from WEBSITE limit ?, ?

I hope it will help

Upvotes: 13

Pavel
Pavel

Reputation: 1647

try:

declare @row_count int
select @row_count = count(*), URL from WEBSITE limit ?, ?
select @row_count

Upvotes: 0

Levin
Levin

Reputation: 2015

"RESULT" is going to include every record in WEBSITE, right? So just "select count(*) from WEBSITE".

I would expect 

 with result as(...what you have...)
 select count(*) from result;

To work too, with some unnecessary work. What does that not do that you want?

Upvotes: 0

Related Questions