loading..
   English
18:29

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
Bookmark and Share
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
1 http://gos-web.ru/ - . . https://exchangesumo.com/obmen/to/KKBKZT/
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100