loading..
Русский    English
13:41
go

Paging records page 4

New features of Standard, which have been included into  A database management system (DBMS) by Microsoft Corporation. SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Server 2012, make paging a very simple procedure. We are talking about new optional keywords in ORDER BY clause, namely OFFSET and FETCH. These new keywords allow you to specify how many rows from the query result have to be returned to client and from which row they should be begun.

Now this expanded syntax of ORDER BY clause takes the form:

  1. ORDER BY {< expression >
  2.     [ ASC | DESC ]}
  3.     [ ,...n ] 
  4. [
  5. OFFSET < integer_expression_1 > { ROW | ROWS }
  6. [FETCH { FIRST | NEXT } < integer_expression_2 > { ROW | ROWS } ONLY]
  7. ]

FIRST and NEXT are synonyms as well as ROW and ROWS, i.e. you can use any of two variants.

integer_expression_2 presents a number of rows which have to be returned, whereas integer_expression_1 is a number of rows from the beginning of sorted result set, which need to be missed before return. If FETCH keyword is absent, all rows will be returned beginning from integer_expression_1 + 1.

With aid of these new features, paging procedure which has been considered above can be rewritten in a very simple manner:

  1. CREATE PROC paging
  2.   @n int =2 -- number of rows per page, 2 by default
  3. , @p int =1 -- number of page to return, first by default
  4. AS
  5. SELECT * FROM Laptop
  6. ORDER BY price DESC OFFSET @n*(@p-1) ROWS FETCH NEXT @n ROWS ONLY;

Note that standard syntax of ORDER BY clause is supported by PostgreSQL also.

Suggested exercises: 107
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.