Get number of records count before apply top clsuse in sql server

Question

I need to know the number of records (just the count) for a query and then apply the top clause in the query to get the top selected rows. the reason for this is i need this data for pagination. so for example

Select * from Table Where a = 10

will give me 100000 rows. i need that 100000 row count information and then i want to get only top 10 records

this i need for the pagination purpose where in application side i am showing 10 of 100000 records.

Note – this i have to implement in SQL Server 2000 i want to get the output (both count and resultset) in single go. i.e i do not want to hold the resuletset in any temp table or do not want to execute the query multiple time

i really think you should have done some research before posting your question , However try this link http://blog.sqlauthority.com/2013/04/14/sql-server-tricks-for-row-offset-and-paging-in-various-versions-of-sql-server/ — Suraj Singh, Feb 07 '14 at 06:48
possible duplicate of [What is the best way to paginate results in SQL Server](http://stackoverflow.com/questions/109232/what-is-the-best-way-to-paginate-results-in-sql-server) — Suraj Singh, Feb 07 '14 at 06:49
If you need to get the count, and you are dealing with a subset of rows you will need to either run the query twice or you will need to store the rows in a temp table. There's just no getting around that. The code that BAdmin shows will give you an excellent start to work from. — mrdenny, Feb 07 '14 at 07:40

score 0 · Answer 1 · answered Feb 07 '14 at 06:38

You can keep the Count in a variable and then use that. e.g,

DECLARE @Count INT

SELECT @COUNT = COUNT(ID) FROM TABLE

Then use that @COUNT like,

SELECT TOP @COUNT FROM TABLE2

Use the whole in a Stored Procedure and you will get the result in one go.

score 0 · Answer 2 · answered Feb 07 '14 at 06:49

in sql server 2005 and above you can use Row_number rank function and write as:

CREATE PROCEDURE sprocSelectList
  @vara int,
  @startRowIndex int,
  @pageSize int
AS

  SELECT
  *-- all your columns except row column created below
  FROM
    (SELECT ROW_NUMBER() OVER (ORDER BY '--primary key column of your table') AS Row,
     *
    FROM Table) AS TableRows
  WHERE
    (Row between (@startRowIndex) AND @startRowIndex + @pageSize - 1)
    AND a = @vara -- say 10 as you have given

OP mentioned he uses SQL2000, so your solution won't work I'm afraid — NickyvV, Feb 07 '14 at 06:59

score 0 · Answer 3 · answered Feb 07 '14 at 14:24

Sql-server 2000 has it's limitations. But try these 2 solutions:

select top 10 t.*,  x.cnt
from Table t 
join
(select count(*) cnt from table where a = 10) x
on 1 = 1
Where t.a = 10

or

select top 10 t.*, (select count(*) from table where a = t.a) cnt
from Table t
Where t.a = 10

Get number of records count before apply top clsuse in sql server

3 Answers3