Select (SQL): Difference between revisions

Revision as of 19:13, 3 February 2006

Revision as of 19:17, 3 February 2006

Line 100:

* By intoducing ''window functions'' to the SELECT-statement

=== ROW_NUMBER() window function ===

Several ''window functions'' exist. <code>ROW_NUMBER() OVER</code> may be used for a ''simple limit'' on the returned rows. E.g., to return no more than ten rows:

Line 112 ⟶ 113:

</pre>

NOTE: ROW_NUMBER is [[Nondeterministic algorithm|non-deterministic]], unless ''key'' is unique. Each row will always get a unique row number. HoeverHowever if ''key'' is not unique, each time you run the query it is possible to get different row numbers assigned to any rows where ''key'' is the same.

=== RANK() window function ===

The <code>RANK() OVER</code> window function acts like ROW_NUMBER, but may return more than ''n'' rows in case of tie conditions. E.g., to return the top-10 youngest persons:

Line 129 ⟶ 132:

The above code could return more than ten rows, e.g. if there are eleven people of the same age.

=== Non-standard syntax ===

Not all DBMSes support the mentioned window functions, and non-standard syntax has to be used. Below, variants of the ''simple limit'' query for different DBMSes are listed: