CODEWITHSUNDEEP
sqlsql-servergroup-bypagination
Milo Cabs
Milo Cabs

Reputation: 513

SQL paging a query that has a group by clause

Is it possible to do a paging type of query where my main query has a group by clause in it.

How would I implement this (if this is even possible)

Example query: 

SELECT * FROM TABLE_NAME GROUP BY DATEPART(YEAR,DATE), DATEPART(YEAR,MONTH)

Can I possibly get the result via reading it page by page?

...and while on the topic, can I also get the total number of rows that would be retrieved without putting it inside a sub query?

Upvotes: 1

Views: 2770

Answers (2)

Shailesh
Shailesh

Reputation: 1218

Use ROW_NUMBER & CTE feature of SQL Server as mentioned below.

WITH CTE AS (
SELECT     A,B,C
           ROW_NUMBER() OVER (ORDER BY  columnName) RN
)
SELECT * FROM CTE WHERE RN Between 1 and 10

Please refer http://sqlserverplanet.com/sql/pagination-using-rownumber for more details.

Upvotes: 2

user1149244
user1149244

Reputation: 755

The concept of pagination is :

  1. You need to get the total number of rows.

    SELECT * FROM table_name where id='id';

  2. Set the number of items to be show per page. Like $limit=50. This mean, you will show 50 items per page.

Hmm, I think it should be better if you'll take a look at this one, http://php.about.com/od/phpwithmysql/ss/php_pagination.htm. It's explained much more better.

Hope that helps.

Upvotes: -1

Related Questions