Select (SQL): Difference between revisions

Revision as of 12:37, 3 February 2006

Revision as of 19:13, 3 February 2006

Line 96:

== Limiting result rows ==

In [[International Organization for Standardization|ISO]] SQL-2003, result sets may be limited by using

* [[Cursor (databases)|cursors]], or

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

Line 112:

</pre>

NOTE: ROW_NUMBER is [[Nondeterministic algorithm|non-deterministic]], unless ''key'' is unique. Each row will always get a unique row number. Hoever 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.

The above code is dangerous, because more than one row might qualify for the 10th position.

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 137:

|-

| [[DB2]]

| (Supports the standard, since DB2 Version 6)

|-

| [[Informix]]

Line 152:

|-

| [[Oracle]]

| (Supports the standard, since Oracle8i)

|}