CTE problem

• Oana Schuster

  SSCommitted

  Points: 1642

  More actions

  January 31, 2008 at 5:23 am

  #378229

  Hi all,

  I have a small problem or maybe I'm just missing something ...

  I have the following script:

  WITH CTE_Table AS

  (

  SELECT

  ROW_NUMBER() OVER (ORDER BY createdatetime) AS RowNum,

  id,

  title,

  author

  FROM

  TBL_Book b

  INNER JOIN

  TBL_BookStatus bs ON bs.BookId = b.Id

  WHERE 1 = 1 AND b.title LIKE '%a%'

  )

  SELECT TOP(100) *

  FROM

  CTE_Table

  WHERE

  RowNum BETWEEN (@PageNumber - 1) * @PageSize+ 1

  AND @PageNumber * @PageSize

  this part is constructed using dynamic sql.

  at the end i am selecting TOP(100) rows .. because this is the maximum rows which will be shown at the end .

  After filtering the data (applying the WHERE clause) the first select statement can retrieve 1500 rows ..and i don't know how to get that number (the total rows) in one shot. Can I return it by adding it as a column to the list of columns selected?

  something like SELECT count(*), list_of_columns ... so on???

  I would appreciate a little bit of help on this.

  Kind Regards ,

  Oana Schuster.

• Mark Cowne

  One Orange Chip

  Points: 26954

  More actions

  January 31, 2008 at 5:41 am

  #773691

  Maybe this?

  SELECT

  ROW_NUMBER() OVER (ORDER BY createdatetime) AS RowNum,

  COUNT(*) OVER() AS TotalRows,

  id,

  ...

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Oana Schuster

  SSCommitted

  Points: 1642

  More actions

  January 31, 2008 at 6:02 am

  #773694

  Hi,

  If I add that part the script is taking forever to run 🙁 from 2 seconds without it ... to 50 seconds ...

  :crying:

  Regards,

  Oana.