loading..
Ðóññêèé    English
05:20
go

Paging records page 2

To calculate the number of pages, we can use following simple algorithm:

  • If the number of query rows divisible by the number of records per page, the result of integer division one to the other gives the number of pages;
  • if there is a nonzero remainder of integer division of the query rows on the number of records on the page, the result of integer division adds one (the last page is incomplete).

This algorithm is implemented by standard means using the CASE operator:

Console
Execute
  1. SELECT *, 
  2.       CASE WHEN total % 2 = 0 THEN total/2 ELSE total/2 + 1 END AS num_of_pages
  3. FROM (
  4.       SELECT *, COUNT(*) OVER() AS total 
  5.       FROM Laptop
  6.       ) X;


To get to each row of query the page number on which it must fall, we can apply the same algorithm, but apply it is necessary not to the total number of rows (total), and row number. This row number we can get through a ranking ROW_NUMBER function, performing the required sorting by price:

Console
Execute
  1. SELECT *, 
  2.       CASE WHEN num % 2 = 0 THEN num/2 ELSE num/2 + 1 END AS page_num, 
  3.       CASE WHEN total % 2 = 0 THEN total/2 ELSE total/2 + 1 END AS num_of_pages
  4. FROM (
  5.       SELECT *, ROW_NUMBER() OVER(ORDER BY price DESC) AS num, 
  6.              COUNT(*) OVER() AS total 
  7.       FROM Laptop
  8. ) X;

code model speed ram hd price screen num total page_num num_of_pages
3 1750 754     128    12     1200.00     14     1     6     1     3
5 1752     750     128    10     1150.00     14     2     6     1     3
4 1298     600     64      10     1050.00     15     3     6     2     3
2     1321     500     64      8     970.00     12     4     6     2     3
6     1298     450     64      10     950.00     12     5     6     3     3
1     1298     350     32      4     700.00     11     6     6     3     3
Pages 1 2 3 4
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.