04:21

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

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

SELECT *, 
      CASE WHEN total % 2 = 0 THEN total/2 ELSE total/2 + 1 END AS num_of_pages
FROM (
      SELECT *, COUNT(*) OVER() AS total 
      FROM Laptop
      ) 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

SELECT *, 
      CASE WHEN num % 2 = 0 THEN num/2 ELSE num/2 + 1 END AS page_num, 
      CASE WHEN total % 2 = 0 THEN total/2 ELSE total/2 + 1 END AS num_of_pages
FROM (
      SELECT *, ROW_NUMBER() OVER(ORDER BY price DESC) AS num, 
             COUNT(*) OVER() AS total 
      FROM Laptop
) 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

CASE ROW_NUMBER OVER window functions paging

Previous [Window functions]

[Other examples of using window functions] Next

Last added:

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
month ago

https://exchangesumo.com/obmen/to/KKBKZT/ . https://www.reg.ru/domain/shop/lot/atomskill.ru

SQL Problems and solutions

S. I. Moiseenko

New sql features

Window functions

Paging records page 2