Razvan Socol
3/24/2007 7:30:00 AM
On Mar 24, 9:56 am, "Janet" <no_sp...@thanks.anyway> wrote:
> Anyone know if there's a built-in equivalent to MySQL's SQL_CALC_FOUND_ROWS
> function?
Hello, Janet
I am not familiar with MySQL, but from what I read, it seems that the
SQL_CALC_FOUND_ROWS function returns how many rows there would be in
the result set, disregarding any LIMIT clause.
For example, let's consider this MySQL query, in the something like
the AdventureWorks database:
SELECT SQL_CALC_FOUND_ROWS as totalrows, FirstName, LastName
FROM Person.Contact WHERE Title='Mr.'
ORDER BY FirstName, LastName
LIMIT 0, 10
An equivalent query in SQL Server 2005 would be:
SELECT totalrows, FirstName, LastName FROM (
SELECT COUNT(*) OVER () totalrows, FirstName, LastName,
ROW_NUMBER() OVER (ORDER BY FirstName, LastName) rn
FROM Person.Contact WHERE Title='Mr.'
) x WHERE rn BETWEEN 1 AND 10
ORDER BY FirstName, LastName;
Another way would be:
WITH X AS (
SELECT COUNT(*) OVER () totalrows, FirstName, LastName,
ROW_NUMBER() OVER (ORDER BY FirstName, LastName) rn
FROM Person.Contact WHERE Title='Mr.'
)
SELECT TOP 10 (SELECT COUNT(*) FROM X) totalrows, FirstName, LastName
FROM X ORDER BY FirstName, LastName
Razvan