Documentation Source Text

Check-in [1c7ab913f0]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Improved linkage to window functions.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | branch-3.25
Files: files | file ages | folders
SHA3-256: 1c7ab913f0bfadf4a739a7fe4b7a741c14310d6fd7dbfd04fde9126be4240dc3
User & Date: drh 2018-09-24 14:38:10.232
Context
2018-09-24
14:46
Clarify the operation of the "weekday" date and time modifier. (check-in: b4b8f35ef1 user: drh tags: branch-3.25)
14:38
Improved linkage to window functions. (check-in: 1c7ab913f0 user: drh tags: branch-3.25)
2018-09-21
10:16
Fix a harmless typo in the json documentation. (check-in: 2bdb2d7e21 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to common_links.tcl.
8
9
10
11
12
13
14

15
16
17
18
19
20
21
<li> <a href="chronology.html">Prior Releases</a>
<li> <a href="lang.html">SQL Syntax</a>
<ul>
<li> <a href="pragma.html#toc">Pragmas</a>
<li> <a href="lang_corefunc.html">SQL functions</a>
<li> <a href="lang_datefunc.html">Date &amp; time functions</a>
<li> <a href="lang_aggfunc.html">Aggregate functions</a>

<li> <a href="json1.html">JSON functions</a>
</ul>
</li>
<li> <a href="c3ref/intro.html">C/C++ Interface Spec</a>
<ul>
<li> <a href="cintro.html">Introduction</a>
<li> <a href="c3ref/funclist.html">List of C-language APIs</a>







>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<li> <a href="chronology.html">Prior Releases</a>
<li> <a href="lang.html">SQL Syntax</a>
<ul>
<li> <a href="pragma.html#toc">Pragmas</a>
<li> <a href="lang_corefunc.html">SQL functions</a>
<li> <a href="lang_datefunc.html">Date &amp; time functions</a>
<li> <a href="lang_aggfunc.html">Aggregate functions</a>
<li> <a href="windowfunctions.html#biwinfunc">Window functions</a>
<li> <a href="json1.html">JSON functions</a>
</ul>
</li>
<li> <a href="c3ref/intro.html">C/C++ Interface Spec</a>
<ul>
<li> <a href="cintro.html">Introduction</a>
<li> <a href="c3ref/funclist.html">List of C-language APIs</a>
Changes to pages/lang.in.
2386
2387
2388
2389
2390
2391
2392
2393

2394
2395

2396
2397
2398
2399
2400
2401
2402
<p>The boolean identifiers TRUE and FALSE are usually just aliases for
the integer values 1 and 0, respectively.  However, if TRUE or FALSE
occur on the right-hand side of an IS operator, then they form new
unary postfix operators "IS TRUE" and "IS FALSE" which test the boolean
value of the operand on the left.

<h3>Functions</h3>
<p>SQLite supports many [corefunc|simple] and [aggfunc|aggregate]

SQL functions.  For presentation purposes, simple functions are further
subdivided into [corefunc | core functions] and [datefunc|date-time functions].

Applications can add new functions, written in C/C++, using the
[sqlite3_create_function()] interface.
</p>

<p>^It is possible to have an aggregate function with the same name as a
simple function, as long as the number of arguments for the two forms of the
function are different.  ^For example, the [agg_max|max()] function with a







|
>

|
>







2386
2387
2388
2389
2390
2391
2392
2393
2394
2395
2396
2397
2398
2399
2400
2401
2402
2403
2404
<p>The boolean identifiers TRUE and FALSE are usually just aliases for
the integer values 1 and 0, respectively.  However, if TRUE or FALSE
occur on the right-hand side of an IS operator, then they form new
unary postfix operators "IS TRUE" and "IS FALSE" which test the boolean
value of the operand on the left.

<h3>Functions</h3>
<p>SQLite supports many [corefunc|simple], [aggfunc|aggregate],
and [window functions|window]
SQL functions.  For presentation purposes, simple functions are further
subdivided into [corefunc | core functions], [datefunc|date-time functions],
and [json1|JSON functions].
Applications can add new functions, written in C/C++, using the
[sqlite3_create_function()] interface.
</p>

<p>^It is possible to have an aggregate function with the same name as a
simple function, as long as the number of arguments for the two forms of the
function are different.  ^For example, the [agg_max|max()] function with a
2785
2786
2787
2788
2789
2790
2791
2792

2793
2794
2795
2796
2797
2798
2799
  ^This SQL function is implemented using the [sqlite3_result_zeroblob()]
  routine from the C/C++ interface.
}
</tcl>

<p>The core functions shown below are available by default. 
[datefunc | Date &amp; Time functions],
[aggfunc | aggregate functions], and

[json1 | JSON functions] are documented separately.  An
application may define additional
functions written in C and added to the database engine using
the [sqlite3_create_function()] API.</p>

<tcl>
set lx {}







|
>







2787
2788
2789
2790
2791
2792
2793
2794
2795
2796
2797
2798
2799
2800
2801
2802
  ^This SQL function is implemented using the [sqlite3_result_zeroblob()]
  routine from the C/C++ interface.
}
</tcl>

<p>The core functions shown below are available by default. 
[datefunc | Date &amp; Time functions],
[aggfunc | aggregate functions],
[builtin window functions|window functions], and
[json1 | JSON functions] are documented separately.  An
application may define additional
functions written in C and added to the database engine using
the [sqlite3_create_function()] API.</p>

<tcl>
set lx {}
Changes to pages/windowfunctions.in.
313
314
315
316
317
318
319

320
321
322
323
324
325
326
for all ORDER BY expressions are considered peers regardless of the frame type.

<p> Most built-in window functions ignore the
<yynonterm>frame-spec</yynonterm>, the exceptions being first_value(),
last_value() and nth_value(). It is a syntax error to specify a FILTER 
clause as part of a built-in window function invocation.


<p> SQLite supports the following 11 built-in window functions:

<dl>
  <dt><p><b>row_number()</b>
  <dd><p> The number of the row within the current partition. Rows are
       numbered starting from 1 in the order defined by the ORDER BY clause in
       the window definition, or in arbitrary order otherwise.







>







313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
for all ORDER BY expressions are considered peers regardless of the frame type.

<p> Most built-in window functions ignore the
<yynonterm>frame-spec</yynonterm>, the exceptions being first_value(),
last_value() and nth_value(). It is a syntax error to specify a FILTER 
clause as part of a built-in window function invocation.

<tcl>hd_fragment biwinfunc {builtin window functions}</tcl>
<p> SQLite supports the following 11 built-in window functions:

<dl>
  <dt><p><b>row_number()</b>
  <dd><p> The number of the row within the current partition. Rows are
       numbered starting from 1 in the order defined by the ORDER BY clause in
       the window definition, or in arbitrary order otherwise.