The WHERE 9 clause allows you to specify one or more subqueries that can be referenced by name in the primary query. The subqueries effectively act as temporary tables or views for the duration of the primary query. Each subquery can be a GROUP BY [ ALL | DISTINCT ] 1, GROUP BY [ ALL | DISTINCT ] 2, GROUP BY [ ALL | DISTINCT ] 3, GROUP BY [ ALL | DISTINCT ] 4, GROUP BY [ ALL | DISTINCT ] 5 or GROUP BY [ ALL | DISTINCT ] 6 statement. When writing a data-modifying statement (GROUP BY [ ALL | DISTINCT ] 4, GROUP BY [ ALL | DISTINCT ] 5 or GROUP BY [ ALL | DISTINCT ] 6) in WHERE 9, it is usual to include a HAVING 1 clause. It is the output of HAVING 1, not the underlying table that the statement modifies, that forms the temporary table that is read by the primary query. If HAVING 1 is omitted, the statement is still executed, but it produces no output so it cannot be referenced as a table by the primary query. Show
A name (without schema qualification) must be specified for each WHERE 9 query. Optionally, a list of column names can be specified; if this is omitted, the column names are inferred from the subquery.If HAVING 5 is specified, it allows a GROUP BY [ ALL | DISTINCT ] 1 subquery to reference itself by name. Such a subquery must have the form
where the recursive self-reference must appear on the right-hand side of the HAVING 7. Only one recursive self-reference is permitted per query. Recursive data-modifying statements are not supported, but you can use the results of a recursive GROUP BY [ ALL | DISTINCT ] 1 query in a data-modifying statement. See Section 7.8 for an example.Another effect of HAVING 5 is that WHERE 9 queries need not be ordered: a query can reference another one that is later in the list. (However, circular references, or mutual recursion, are not implemented.) Without HAVING 5, WHERE 9 queries can only reference sibling WHERE 9 queries that are earlier in the WHERE 9 list.When there are multiple queries in the WHERE 9 clause, HAVING 5 should be written only once, immediately after WHERE 9. It applies to all queries in the WHERE 9 clause, though it has no effect on queries that do not use recursion or forward references.The optional WINDOW9 clause computes a search sequence column that can be used for ordering the results of a recursive query in either breadth-first or depth-first order. The supplied column name list specifies the row key that is to be used for keeping track of visited rows. A column named [0 will be added to the result column list of the WHERE 9 query. This column can be ordered by in the outer query to achieve the respective ordering. See for examples.The optional [2 clause is used to detect cycles in recursive queries. The supplied column name list specifies the row key that is to be used for keeping track of visited rows. A column named [3 will be added to the result column list of the WHERE 9 query. This column will be set to [5 when a cycle has been detected, else to [6. Furthermore, processing of the recursive union will stop when a cycle has been detected. [5 and [6 must be constants and they must be coercible to a common data type, and the data type must have an inequality operator. (The SQL standard requires that they be Boolean constants or character strings, but PostgreSQL does not require that.) By default, [9 and { RANGE | ROWS | GROUPS } 0 (of type { RANGE | ROWS | GROUPS } 1) are used. Furthermore, a column named { RANGE | ROWS | GROUPS } 2 will be added to the result column list of the WHERE 9 query. This column is used internally for tracking visited rows. See for examples.Both the WINDOW9 and the [2 clause are only valid for recursive WHERE 9 queries. The { RANGE | ROWS | GROUPS } 7 must be a HAVING 7 (or { RANGE | ROWS | GROUPS } 9) of two GROUP BY [ ALL | DISTINCT ] 1 (or equivalent) commands (no nested HAVING 7s). If both clauses are used, the column added by the WINDOW9 clause appears before the columns added by the [2 clause. The primary query and the WHERE 9 queries are all (notionally) executed at the same time. This implies that the effects of a data-modifying statement in WHERE 9 cannot be seen from other parts of the query, other than by reading its HAVING 1 output. If two such data-modifying statements attempt to modify the same row, the results are unspecified.A key property of WHERE 9 queries is that they are normally evaluated only once per execution of the primary query, even if the primary query refers to them more than once. In particular, data-modifying statements are guaranteed to be executed once and only once, regardless of whether the primary query reads all or any of their output.However, a WHERE 9 query can be marked UNBOUNDED PRECEDING9 to remove this guarantee. In that case, the WHERE 9 query can be folded into the primary query much as though it were a simple sub-GROUP BY [ ALL | DISTINCT ] 1 in the primary query's EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS2 clause. This results in duplicate computations if the primary query refers to that WHERE 9 query more than once; but if each such use requires only a few rows of the WHERE 9 query's total output, UNBOUNDED PRECEDING9 can provide a net savings by allowing the queries to be optimized jointly. UNBOUNDED PRECEDING9 is ignored if it is attached to a WHERE 9 query that is recursive or is not side-effect-free (i.e., is not a plain GROUP BY [ ALL | DISTINCT ] 1 containing no volatile functions).By default, a side-effect-free WHERE 9 query is folded into the primary query if it is used exactly once in the primary query's EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS2 clause. This allows joint optimization of the two query levels in situations where that should be semantically invisible. However, such folding can be prevented by marking the WHERE 9 query as SELECT DISTINCT ON (location) location, time, report
FROM weather_reports
ORDER BY location, time DESC;
2. That might be useful, for example, if the WHERE 9 query is being used as an optimization fence to prevent the planner from choosing a bad plan. PostgreSQL versions before v12 never did such folding, so queries written for older versions might rely on WHERE 9 to act as an optimization fence.See Section 7.8 for additional information. EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS 2 ClauseThe EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS2 clause specifies one or more source tables for the GROUP BY [ ALL | DISTINCT ] 1. If multiple sources are specified, the result is the Cartesian product (cross join) of all the sources. But usually qualification conditions are added (via SELECT DISTINCT ON (location) location, time, report
FROM weather_reports
ORDER BY location, time DESC;
8) to restrict the returned rows to a small subset of the Cartesian product.The EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS2 clause can contain the following elements: WHERE 00The name (optionally schema-qualified) of an existing table or view. If WHERE 01 is specified before the table name, only that table is scanned. If WHERE 01 is not specified, the table and all its descendant tables (if any) are scanned. Optionally, WHERE 03 can be specified after the table name to explicitly indicate that descendant tables are included.WHERE 04A substitute name for the EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS2 item containing the alias. An alias is used for brevity or to eliminate ambiguity for self-joins (where the same table is scanned multiple times). When an alias is provided, it completely hides the actual name of the table or function; for example given WHERE 06, the remainder of the GROUP BY [ ALL | DISTINCT ] 1 must refer to this EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS2 item as WHERE 09 not WHERE 10. If an alias is written, a column alias list can also be written to provide substitute names for one or more columns of the table.WHERE 11 ( WHERE 12 [, ...] ) [ REPEATABLE ( WHERE 13 ) ]A WHERE 14 clause after a WHERE 00 indicates that the specified WHERE 16 should be used to retrieve a subset of the rows in that table. This sampling precedes the application of any other filters such as SELECT DISTINCT ON (location) location, time, report
FROM weather_reports
ORDER BY location, time DESC;
8 clauses. The standard PostgreSQL distribution includes two sampling methods, WHERE 18 and WHERE 19, and other sampling methods can be installed in the database via extensions.The WHERE 18 and WHERE 19 sampling methods each accept a single WHERE 12 which is the fraction of the table to sample, expressed as a percentage between 0 and 100. This argument can be any WHERE 23-valued expression. (Other sampling methods might accept more or different arguments.) These two methods each return a randomly-chosen sample of the table that will contain approximately the specified percentage of the table's rows. The WHERE 18 method scans the whole table and selects or ignores individual rows independently with the specified probability. The WHERE 19 method does block-level sampling with each block having the specified chance of being selected; all rows in each selected block are returned. The WHERE 19 method is significantly faster than the WHERE 18 method when small sampling percentages are specified, but it may return a less-random sample of the table as a result of clustering effects.The optional WHERE 28 clause specifies a WHERE 13 number or expression to use for generating random numbers within the sampling method. The seed value can be any non-null floating-point value. Two queries that specify the same seed and WHERE 12 values will select the same sample of the table, if the table has not been changed meanwhile. But different seed values will usually produce different samples. If WHERE 28 is not given then a new random sample is selected for each query, based upon a system-generated seed. Note that some add-on sampling methods do not accept WHERE 28, and will always produce new samples on each use.WHERE 33A sub- GROUP BY [ ALL | DISTINCT ] 1 can appear in the EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS2 clause. This acts as though its output were created as a temporary table for the duration of this single GROUP BY [ ALL | DISTINCT ] 1 command. Note that the sub-GROUP BY [ ALL | DISTINCT ] 1 must be surrounded by parentheses, and an alias must be provided for it. A GROUP BY [ ALL | DISTINCT ] 3 command can also be used here.WHERE 39A WHERE 9 query is referenced by writing its name, just as though the query's name were a table name. (In fact, the WHERE 9 query hides any real table of the same name for the purposes of the primary query. If necessary, you can refer to a real table of the same name by schema-qualifying the table's name.) An alias can be provided in the same way as for a table.WHERE 42Function calls can appear in the EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS2 clause. (This is especially useful for functions that return result sets, but any function can be used.) This acts as though the function's output were created as a temporary table for the duration of this single GROUP BY [ ALL | DISTINCT ] 1 command. If the function's result type is composite (including the case of a function with multiple WHERE 45 parameters), each attribute becomes a separate column in the implicit table.When the optional WHERE 46 clause is added to the function call, an additional column of type WHERE 47 will be appended to the function's result column(s). This column numbers the rows of the function's result set, starting from 1. By default, this column is named WHERE 48.An alias can be provided in the same way as for a table. If an alias is written, a column alias list can also be written to provide substitute names for one or more attributes of the function's composite return type, including the ordinality column if present. Multiple function calls can be combined into a single EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS2-clause item by surrounding them with WHERE 50. The output of such an item is the concatenation of the first row from each function, then the second row from each function, etc. If some of the functions produce fewer rows than others, null values are substituted for the missing data, so that the total number of rows returned is always the same as for the function that produced the most rows.If the function has been defined as returning the WHERE 51 data type, then an alias or the key word WHERE 52 must be present, followed by a column definition list in the form WHERE 53 WHERE 54 [, ... ]). The column definition list must match the actual number and types of columns returned by the function.When using the WHERE 50 syntax, if one of the functions requires a column definition list, it's preferred to put the column definition list after the function call inside WHERE 50. A column definition list can be placed after the WHERE 50 construct only if there's just a single function and no WHERE 46 clause.To use WHERE 59 together with a column definition list, you must use the WHERE 50 syntax and put the column definition list inside WHERE 50.WHERE 62One of
For the WHERE 67 and WHERE 68 join types, a join condition must be specified, namely exactly one of WHERE 69, WHERE 70 [, ...]), or WHERE 71. See below for the meaning.A WHERE 72 clause combines two EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS2 items, which for convenience we will refer to as “tables”, though in reality they can be any type of EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS2 item. Use parentheses if necessary to determine the order of nesting. In the absence of parentheses, WHERE 72s nest left-to-right. In any case WHERE 72 binds more tightly than the commas separating EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS2-list items. All the WHERE 72 options are just a notational convenience, since they do nothing you couldn't do with plain EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS2 and SELECT DISTINCT ON (location) location, time, report
FROM weather_reports
ORDER BY location, time DESC;
8.WHERE 81 returns all rows in the qualified Cartesian product (i.e., all combined rows that pass its join condition), plus one copy of each row in the left-hand table for which there was no right-hand row that passed the join condition. This left-hand row is extended to the full width of the joined table by inserting null values for the right-hand columns. Note that only the WHERE 72 clause's own condition is considered while deciding which rows have matches. Outer conditions are applied afterwards.Conversely, WHERE 83 returns all the joined rows, plus one row for each unmatched right-hand row (extended with nulls on the left). This is just a notational convenience, since you could convert it to a WHERE 81 by switching the left and right tables.WHERE 85 returns all the joined rows, plus one row for each unmatched left-hand row (extended with nulls on the right), plus one row for each unmatched right-hand row (extended with nulls on the left).WHERE 69WHERE 87 is an expression resulting in a value of type { RANGE | ROWS | GROUPS } 1 (similar to a SELECT DISTINCT ON (location) location, time, report
FROM weather_reports
ORDER BY location, time DESC;
8 clause) that specifies which rows in a join are considered to match.WHERE 90 [, ...] ) [ AS WHERE 91 ]A clause of the form WHERE 92 is shorthand for WHERE 93. Also, WHERE 94 implies that only one of each pair of equivalent columns will be included in the join output, not both.If a WHERE 91 name is specified, it provides a table alias for the join columns. Only the join columns listed in the WHERE 94 clause are addressable by this name. Unlike a regular WHERE 04, this does not hide the names of the joined tables from the rest of the query. Also unlike a regular WHERE 04, you cannot write a column alias list — the output names of the join columns are the same as they appear in the WHERE 94 list.WHERE 71WHERE 71 is shorthand for a WHERE 94 list that mentions all columns in the two tables that have matching names. If there are no common column names, WHERE 71 is equivalent to GROUP BY [ ALL | DISTINCT ] 04.GROUP BY [ ALL | DISTINCT ] 05GROUP BY [ ALL | DISTINCT ] 05 is equivalent to GROUP BY [ ALL | DISTINCT ] 07, that is, no rows are removed by qualification. They produce a simple Cartesian product, the same result as you get from listing the two tables at the top level of EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS2, but restricted by the join condition (if any). GROUP BY [ ALL | DISTINCT ] 09The GROUP BY [ ALL | DISTINCT ] 09 key word can precede a sub-GROUP BY [ ALL | DISTINCT ] 1 EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS2 item. This allows the sub- GROUP BY [ ALL | DISTINCT ] 1 to refer to columns of EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS2 items that appear before it in the EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS2 list. (Without GROUP BY [ ALL | DISTINCT ] 09, each sub-GROUP BY [ ALL | DISTINCT ] 1 is evaluated independently and so cannot cross-reference any other EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS2 item.) GROUP BY [ ALL | DISTINCT ] 09 can also precede a function-call EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS2 item, but in this case it is a noise word, because the function expression can refer to earlier EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS2 items in any case. A GROUP BY [ ALL | DISTINCT ] 09 item can appear at top level in the EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS2 list, or within a WHERE 72 tree. In the latter case it can also refer to any items that are on the left-hand side of a WHERE 72 that it is on the right-hand side of.When a EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS2 item contains GROUP BY [ ALL | DISTINCT ] 09 cross-references, evaluation proceeds as follows: for each row of the EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS2 item providing the cross-referenced column(s), or set of rows of multiple EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS2 items providing the columns, the GROUP BY [ ALL | DISTINCT ] 09 item is evaluated using that row or row set's values of the columns. The resulting row(s) are joined as usual with the rows they were computed from. This is repeated for each row or set of rows from the column source table(s).The column source table(s) must be WHERE 67 or GROUP BY [ ALL | DISTINCT ] 32 joined to the GROUP BY [ ALL | DISTINCT ] 09 item, else there would not be a well-defined set of rows from which to compute each set of rows for the GROUP BY [ ALL | DISTINCT ] 09 item. Thus, although a construct such as GROUP BY [ ALL | DISTINCT ] 35 RIGHT JOIN LATERAL GROUP BY [ ALL | DISTINCT ] 36 is syntactically valid, it is not actually allowed for GROUP BY [ ALL | DISTINCT ] 36 to reference GROUP BY [ ALL | DISTINCT ] 38.SELECT DISTINCT ON (location) location, time, report FROM weather_reports ORDER BY location, time DESC; 8 ClauseThe optional SELECT DISTINCT ON (location) location, time, report
FROM weather_reports
ORDER BY location, time DESC;
8 clause has the general formWHERE where GROUP BY [ ALL | DISTINCT ] 41 is any expression that evaluates to a result of type { RANGE | ROWS | GROUPS } 1. Any row that does not satisfy this condition will be eliminated from the output. A row satisfies the condition if it returns true when the actual row values are substituted for any variable references.GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] 43 ClauseThe optional GROUP BY [ ALL | DISTINCT ] 43 clause has the general formGROUP BY [ ALL | DISTINCT ] GROUP BY [ ALL | DISTINCT ] 43 will condense into a single row all selected rows that share the same values for the grouped expressions. An GROUP BY [ ALL | DISTINCT ] 46 used inside a GROUP BY [ ALL | DISTINCT ] 47 can be an input column name, or the name or ordinal number of an output column (GROUP BY [ ALL | DISTINCT ] 1 list item), or an arbitrary expression formed from input-column values. In case of ambiguity, a GROUP BY [ ALL | DISTINCT ] 43 name will be interpreted as an input-column name rather than an output column name.If any of GROUP BY [ ALL | DISTINCT ] 50, GROUP BY [ ALL | DISTINCT ] 51 or GROUP BY [ ALL | DISTINCT ] 52 are present as grouping elements, then the GROUP BY [ ALL | DISTINCT ] 43 clause as a whole defines some number of independent GROUP BY [ ALL | DISTINCT ] 54. The effect of this is equivalent to constructing a { RANGE | ROWS | GROUPS } 9 between subqueries with the individual grouping sets as their GROUP BY [ ALL | DISTINCT ] 43 clauses. The optional GROUP BY [ ALL | DISTINCT ] 57 clause removes duplicate sets before processing; it does not transform the { RANGE | ROWS | GROUPS } 9 into a GROUP BY [ ALL | DISTINCT ] 59. For further details on the handling of grouping sets see .Aggregate functions, if any are used, are computed across all rows making up each group, producing a separate value for each group. (If there are aggregate functions but no GROUP BY [ ALL | DISTINCT ] 43 clause, the query is treated as having a single group comprising all the selected rows.) The set of rows fed to each aggregate function can be further filtered by attaching a GROUP BY [ ALL | DISTINCT ] 61 clause to the aggregate function call; see for more information. When a GROUP BY [ ALL | DISTINCT ] 61 clause is present, only those rows matching it are included in the input to that aggregate function.When GROUP BY [ ALL | DISTINCT ] 43 is present, or any aggregate functions are present, it is not valid for the GROUP BY [ ALL | DISTINCT ] 1 list expressions to refer to ungrouped columns except within aggregate functions or when the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column. A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.Keep in mind that all aggregate functions are evaluated before evaluating any “scalar” expressions in the GROUP BY [ ALL | DISTINCT ] 65 clause or GROUP BY [ ALL | DISTINCT ] 1 list. This means that, for example, a GROUP BY [ ALL | DISTINCT ] 67 expression cannot be used to skip evaluation of an aggregate function; see .Currently, GROUP BY [ ALL | DISTINCT ] 68, GROUP BY [ ALL | DISTINCT ] 69, GROUP BY [ ALL | DISTINCT ] 70 and GROUP BY [ ALL | DISTINCT ] 71 cannot be specified with GROUP BY [ ALL | DISTINCT ] 43.GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] 65 ClauseThe optional GROUP BY [ ALL | DISTINCT ] 65 clause has the general formHAVING where GROUP BY [ ALL | DISTINCT ] 41 is the same as specified for the SELECT DISTINCT ON (location) location, time, report
FROM weather_reports
ORDER BY location, time DESC;
8 clause.GROUP BY [ ALL | DISTINCT ] 65 eliminates group rows that do not satisfy the condition. GROUP BY [ ALL | DISTINCT ] 65 is different from SELECT DISTINCT ON (location) location, time, report
FROM weather_reports
ORDER BY location, time DESC;
8: SELECT DISTINCT ON (location) location, time, report
FROM weather_reports
ORDER BY location, time DESC;
8 filters individual rows before the application of GROUP BY [ ALL | DISTINCT ] 43, while GROUP BY [ ALL | DISTINCT ] 65 filters group rows created by GROUP BY [ ALL | DISTINCT ] 43. Each column referenced in GROUP BY [ ALL | DISTINCT ] 41 must unambiguously reference a grouping column, unless the reference appears within an aggregate function or the ungrouped column is functionally dependent on the grouping columns.The presence of GROUP BY [ ALL | DISTINCT ] 65 turns a query into a grouped query even if there is no GROUP BY [ ALL | DISTINCT ] 43 clause. This is the same as what happens when the query contains aggregate functions but no GROUP BY [ ALL | DISTINCT ] 43 clause. All the selected rows are considered to form a single group, and the GROUP BY [ ALL | DISTINCT ] 1 list and GROUP BY [ ALL | DISTINCT ] 65 clause can only reference table columns from within aggregate functions. Such a query will emit a single row if the GROUP BY [ ALL | DISTINCT ] 65 condition is true, zero rows if it is not true.Currently, GROUP BY [ ALL | DISTINCT ] 68, GROUP BY [ ALL | DISTINCT ] 69, GROUP BY [ ALL | DISTINCT ] 70 and GROUP BY [ ALL | DISTINCT ] 71 cannot be specified with GROUP BY [ ALL | DISTINCT ] 65.GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] 96 ClauseThe optional GROUP BY [ ALL | DISTINCT ] 96 clause has the general formWINDOW where GROUP BY [ ALL | DISTINCT ] 98 is a name that can be referenced from GROUP BY [ ALL | DISTINCT ] 99 clauses or subsequent window definitions, and HAVING 00 is[ If an HAVING 01 is specified it must refer to an earlier entry in the GROUP BY [ ALL | DISTINCT ] 96 list; the new window copies its partitioning clause from that entry, as well as its ordering clause if any. In this case the new window cannot specify its own HAVING 03 clause, and it can specify HAVING 04 only if the copied window does not have one. The new window always uses its own frame clause; the copied window must not specify a frame clause.The elements of the HAVING 03 list are interpreted in much the same fashion as elements of a clause, except that they are always simple expressions and never the name or number of an output column. Another difference is that these expressions can contain aggregate function calls, which are not allowed in a regular GROUP BY [ ALL | DISTINCT ] 43 clause. They are allowed here because windowing occurs after grouping and aggregation.Similarly, the elements of the HAVING 04 list are interpreted in much the same fashion as elements of a statement-level clause, except that the expressions are always taken as simple expressions and never the name or number of an output column.The optional HAVING 10 defines the window frame for window functions that depend on the frame (not all do). The window frame is a set of related rows for each row of the query (called the current row). The HAVING 10 can be one of{ RANGE | ROWS | GROUPS } where HAVING 12 and HAVING 13 can be one ofUNBOUNDED PRECEDING and HAVING 14 can be one ofEXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS If HAVING 13 is omitted it defaults to HAVING 16. Restrictions are that HAVING 12 cannot be HAVING 18, HAVING 13 cannot be HAVING 20, and the HAVING 13 choice cannot appear earlier in the above list of HAVING 12 and HAVING 13 options than the HAVING 12 choice does — for example HAVING 25 PRECEDING is not allowed.The default framing option is HAVING 26, which is the same as HAVING 27; it sets the frame to be all rows from the partition start up through the current row's last peer (a row that the window's HAVING 04 clause considers equivalent to the current row; all rows are peers if there is no HAVING 04). In general, HAVING 20 means that the frame starts with the first row of the partition, and similarly HAVING 18 means that the frame ends with the last row of the partition, regardless of HAVING 32, HAVING 33 or HAVING 34 mode. In HAVING 33 mode, HAVING 16 means that the frame starts or ends with the current row; but in HAVING 32 or HAVING 34 mode it means that the frame starts or ends with the current row's first or last peer in the HAVING 04 ordering. The HAVING 40 HAVING 41 and HAVING 40 HAVING 43 options vary in meaning depending on the frame mode. In HAVING 33 mode, the HAVING 40 is an integer indicating that the frame starts or ends that many rows before or after the current row. In HAVING 34 mode, the HAVING 40 is an integer indicating that the frame starts or ends that many peer groups before or after the current row's peer group, where a peer group is a group of rows that are equivalent according to the window's HAVING 04 clause. In HAVING 32 mode, use of an HAVING 40 option requires that there be exactly one HAVING 04 column in the window definition. Then the frame contains those rows whose ordering column value is no more than HAVING 40 less than (for HAVING 41) or more than (for HAVING 43) the current row's ordering column value. In these cases the data type of the HAVING 40 expression depends on the data type of the ordering column. For numeric ordering columns it is typically of the same type as the ordering column, but for datetime ordering columns it is an HAVING 56. In all these cases, the value of the HAVING 40 must be non-null and non-negative. Also, while the HAVING 40 does not have to be a simple constant, it cannot contain variables, aggregate functions, or window functions.The HAVING 14 option allows rows around the current row to be excluded from the frame, even if they would be included according to the frame start and frame end options. HAVING 60 excludes the current row from the frame. HAVING 61 excludes the current row and its ordering peers from the frame. HAVING 62 excludes any peers of the current row from the frame, but not the current row itself. HAVING 63 simply specifies explicitly the default behavior of not excluding the current row or its peers.Beware that the HAVING 33 mode can produce unpredictable results if the HAVING 04 ordering does not order the rows uniquely. The HAVING 32 and HAVING 34 modes are designed to ensure that rows that are peers in the HAVING 04 ordering are treated alike: all rows of a given peer group will be in the frame or excluded from it.The purpose of a GROUP BY [ ALL | DISTINCT ] 96 clause is to specify the behavior of window functions appearing in the query's or clause. These functions can reference the GROUP BY [ ALL | DISTINCT ] 96 clause entries by name in their GROUP BY [ ALL | DISTINCT ] 99 clauses. A GROUP BY [ ALL | DISTINCT ] 96 clause entry does not have to be referenced anywhere, however; if it is not used in the query it is simply ignored. It is possible to use window functions without any GROUP BY [ ALL | DISTINCT ] 96 clause at all, since a window function call can specify its window definition directly in its GROUP BY [ ALL | DISTINCT ] 99 clause. However, the GROUP BY [ ALL | DISTINCT ] 96 clause saves typing when the same window definition is needed for more than one window function.Currently, GROUP BY [ ALL | DISTINCT ] 68, GROUP BY [ ALL | DISTINCT ] 69, GROUP BY [ ALL | DISTINCT ] 70 and GROUP BY [ ALL | DISTINCT ] 71 cannot be specified with GROUP BY [ ALL | DISTINCT ] 96.Window functions are described in detail in Section 3.5, , and . GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] 1 ListThe GROUP BY [ ALL | DISTINCT ] 1 list (between the key words GROUP BY [ ALL | DISTINCT ] 1 and EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS2) specifies expressions that form the output rows of the GROUP BY [ ALL | DISTINCT ] 1 statement. The expressions can (and usually do) refer to columns computed in the EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS2 clause. Just as in a table, every output column of a GROUP BY [ ALL | DISTINCT ] 1 has a name. In a simple GROUP BY [ ALL | DISTINCT ] 1 this name is just used to label the column for display, but when the GROUP BY [ ALL | DISTINCT ] 1 is a sub-query of a larger query, the name is seen by the larger query as the column name of the virtual table produced by the sub-query. To specify the name to use for an output column, write WHERE 52 HAVING 93 after the column's expression. (You can omit WHERE 52, but only if the desired output name does not match any PostgreSQL keyword (see Appendix C). For protection against possible future keyword additions, it is recommended that you always either write WHERE 52 or double-quote the output name.) If you do not specify a column name, a name is chosen automatically by PostgreSQL. If the column's expression is a simple column reference then the chosen name is the same as that column's name. In more complex cases a function or type name may be used, or the system may fall back on a generated name such as HAVING 96.An output column's name can be used to refer to the column's value in HAVING 04 and GROUP BY [ ALL | DISTINCT ] 43 clauses, but not in the SELECT DISTINCT ON (location) location, time, report
FROM weather_reports
ORDER BY location, time DESC;
8 or GROUP BY [ ALL | DISTINCT ] 65 clauses; there you must write out the expression instead.Instead of an expression, WHERE 03 can be written in the output list as a shorthand for all the columns of the selected rows. Also, you can write WHERE 52; the output column names will be the same as the table columns' names.According to the SQL standard, the expressions in the output list should be computed before applying GROUP BY [ ALL | DISTINCT ] 57, HAVING 04, or WINDOW06. This is obviously necessary when using GROUP BY [ ALL | DISTINCT ] 57, since otherwise it's not clear what values are being made distinct. However, in many cases it is convenient if output expressions are computed after HAVING 04 and WINDOW06; particularly if the output list contains any volatile or expensive functions. With that behavior, the order of function evaluations is more intuitive and there will not be evaluations corresponding to rows that never appear in the output. PostgreSQL will effectively evaluate output expressions after sorting and limiting, so long as those expressions are not referenced in GROUP BY [ ALL | DISTINCT ] 57, HAVING 04 or GROUP BY [ ALL | DISTINCT ] 43. (As a counterexample, WINDOW13 clearly must evaluate WINDOW14 before sorting.) Output expressions that contain set-returning functions are effectively evaluated after sorting and before limiting, so that WINDOW06 will act to cut off the output from a set-returning function. NotePostgreSQL versions before 9.6 did not provide any guarantees about the timing of evaluation of output expressions versus sorting and limiting; it depended on the form of the chosen query plan. GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] 57 ClauseIf WINDOW17 is specified, all duplicate rows are removed from the result set (one row is kept from each group of duplicates). WINDOW18 specifies the opposite: all rows are kept; that is the default.
WINDOW20 expressions are interpreted using the same rules as for HAVING 04 (see above). Note that the “first row” of each set is unpredictable unless HAVING 04 is used to ensure that the desired row appears first. For example:SELECT DISTINCT ON (location) location, time, report
FROM weather_reports
ORDER BY location, time DESC;
retrieves the most recent weather report for each location. But if we had not used HAVING 04 to force descending order of time values for each location, we'd have gotten a report from an unpredictable time for each location.The WINDOW20 expression(s) must match the leftmost HAVING 04 expression(s). The HAVING 04 clause will normally contain additional expression(s) that determine the desired precedence of rows within each WINDOW20 group. Currently, GROUP BY [ ALL | DISTINCT ] 68, GROUP BY [ ALL | DISTINCT ] 69, GROUP BY [ ALL | DISTINCT ] 70 and GROUP BY [ ALL | DISTINCT ] 71 cannot be specified with GROUP BY [ ALL | DISTINCT ] 57.HAVING condition 7 ClauseThe HAVING 7 clause has this general form:WHERE 0WINDOW35 is any GROUP BY [ ALL | DISTINCT ] 1 statement without an HAVING 04, WINDOW06, GROUP BY [ ALL | DISTINCT ] 68, GROUP BY [ ALL | DISTINCT ] 69, GROUP BY [ ALL | DISTINCT ] 70, or GROUP BY [ ALL | DISTINCT ] 71 clause. (HAVING 04 and WINDOW06 can be attached to a subexpression if it is enclosed in parentheses. Without parentheses, these clauses will be taken to apply to the result of the HAVING 7, not to its right-hand input expression.)The HAVING 7 operator computes the set union of the rows returned by the involved GROUP BY [ ALL | DISTINCT ] 1 statements. A row is in the set union of two result sets if it appears in at least one of the result sets. The two GROUP BY [ ALL | DISTINCT ] 1 statements that represent the direct operands of the HAVING 7 must produce the same number of columns, and corresponding columns must be of compatible data types.The result of HAVING 7 does not contain any duplicate rows unless the WINDOW51 option is specified. WINDOW51 prevents elimination of duplicates. (Therefore, { RANGE | ROWS | GROUPS } 9 is usually significantly quicker than HAVING 7; use WINDOW51 when you can.) GROUP BY [ ALL | DISTINCT ] 57 can be written to explicitly specify the default behavior of eliminating duplicate rows.Multiple HAVING 7 operators in the same GROUP BY [ ALL | DISTINCT ] 1 statement are evaluated left to right, unless otherwise indicated by parentheses.Currently, GROUP BY [ ALL | DISTINCT ] 68, GROUP BY [ ALL | DISTINCT ] 69, GROUP BY [ ALL | DISTINCT ] 70 and GROUP BY [ ALL | DISTINCT ] 71 cannot be specified either for a HAVING 7 result or for any input of a HAVING 7.WINDOW window_name AS ( window_definition ) [, ...] 65 ClauseThe WINDOW65 clause has this general form: WHERE 1WINDOW35 is any GROUP BY [ ALL | DISTINCT ] 1 statement without an HAVING 04, WINDOW06, GROUP BY [ ALL | DISTINCT ] 68, GROUP BY [ ALL | DISTINCT ] 69, GROUP BY [ ALL | DISTINCT ] 70, or GROUP BY [ ALL | DISTINCT ] 71 clause.The WINDOW65 operator computes the set intersection of the rows returned by the involved GROUP BY [ ALL | DISTINCT ] 1 statements. A row is in the intersection of two result sets if it appears in both result sets.The result of WINDOW65 does not contain any duplicate rows unless the WINDOW51 option is specified. With WINDOW51, a row that has WINDOW80 duplicates in the left table and WINDOW81 duplicates in the right table will appear min( WINDOW80, WINDOW81) times in the result set. GROUP BY [ ALL | DISTINCT ] 57 can be written to explicitly specify the default behavior of eliminating duplicate rows.Multiple WINDOW65 operators in the same GROUP BY [ ALL | DISTINCT ] 1 statement are evaluated left to right, unless parentheses dictate otherwise. WINDOW65 binds more tightly than HAVING 7. That is, WINDOW89 will be read as WINDOW90. Currently, GROUP BY [ ALL | DISTINCT ] 68, GROUP BY [ ALL | DISTINCT ] 69, GROUP BY [ ALL | DISTINCT ] 70 and GROUP BY [ ALL | DISTINCT ] 71 cannot be specified either for an WINDOW65 result or for any input of an WINDOW65. WINDOW window_name AS ( window_definition ) [, ...] 97 ClauseThe WINDOW97 clause has this general form: WHERE 2WINDOW35 is any GROUP BY [ ALL | DISTINCT ] 1 statement without an HAVING 04, WINDOW06, GROUP BY [ ALL | DISTINCT ] 68, GROUP BY [ ALL | DISTINCT ] 69, GROUP BY [ ALL | DISTINCT ] 70, or GROUP BY [ ALL | DISTINCT ] 71 clause.The WINDOW97 operator computes the set of rows that are in the result of the left GROUP BY [ ALL | DISTINCT ] 1 statement but not in the result of the right one.The result of WINDOW97 does not contain any duplicate rows unless the WINDOW51 option is specified. With WINDOW51, a row that has WINDOW80 duplicates in the left table and WINDOW81 duplicates in the right table will appear max( WINDOW80- WINDOW81,0) times in the result set. GROUP BY [ ALL | DISTINCT ] 57 can be written to explicitly specify the default behavior of eliminating duplicate rows.Multiple WINDOW97 operators in the same GROUP BY [ ALL | DISTINCT ] 1 statement are evaluated left to right, unless parentheses dictate otherwise. WINDOW97 binds at the same level as HAVING 7.Currently, GROUP BY [ ALL | DISTINCT ] 68, GROUP BY [ ALL | DISTINCT ] 69, GROUP BY [ ALL | DISTINCT ] 70 and GROUP BY [ ALL | DISTINCT ] 71 cannot be specified either for an WINDOW97 result or for any input of an WINDOW97. HAVING condition 04 ClauseThe optional HAVING 04 clause has this general form:WHERE 3The HAVING 04 clause causes the result rows to be sorted according to the specified expression(s). If two rows are equal according to the leftmost expression, they are compared according to the next expression and so on. If they are equal according to all specified expressions, they are returned in an implementation-dependent order.Each GROUP BY [ ALL | DISTINCT ] 46 can be the name or ordinal number of an output column (GROUP BY [ ALL | DISTINCT ] 1 list item), or it can be an arbitrary expression formed from input-column values.The ordinal number refers to the ordinal (left-to-right) position of the output column. This feature makes it possible to define an ordering on the basis of a column that does not have a unique name. This is never absolutely necessary because it is always possible to assign a name to an output column using the WHERE 52 clause.It is also possible to use arbitrary expressions in the HAVING 04 clause, including columns that do not appear in the GROUP BY [ ALL | DISTINCT ] 1 output list. Thus the following statement is valid:WHERE 4A limitation of this feature is that an HAVING 04 clause applying to the result of a HAVING 7, WINDOW65, or WINDOW97 clause can only specify an output column name or number, not an expression. If an HAVING 04 expression is a simple name that matches both an output column name and an input column name, HAVING 04 will interpret it as the output column name. This is the opposite of the choice that GROUP BY [ ALL | DISTINCT ] 43 will make in the same situation. This inconsistency is made to be compatible with the SQL standard.Optionally one can add the key word [42 (ascending) or [43 (descending) after any expression in the HAVING 04 clause. If not specified, [42 is assumed by default. Alternatively, a specific ordering operator name can be specified in the WHERE 94 clause. An ordering operator must be a less-than or greater-than member of some B-tree operator family. [42 is usually equivalent to [48 and [43 is usually equivalent to [50. (But the creator of a user-defined data type can define exactly what the default sort ordering is, and it might correspond to operators with other names.) If [51 is specified, null values sort after all non-null values; if [52 is specified, null values sort before all non-null values. If neither is specified, the default behavior is [51 when [42 is specified or implied, and [52 when [43 is specified (thus, the default is to act as though nulls are larger than non-nulls). When WHERE 94 is specified, the default nulls ordering depends on whether the operator is a less-than or greater-than operator.Note that ordering options apply only to the expression they follow; for example [58 does not mean the same thing as [59. Character-string data is sorted according to the collation that applies to the column being sorted. That can be overridden at need by including a [60 clause in the GROUP BY [ ALL | DISTINCT ] 46, for example [62. For more information see and Section 24.2. WINDOW window_name AS ( window_definition ) [, ...] 06 ClauseThe WINDOW06 clause consists of two independent sub-clauses: WHERE 5The parameter [65 specifies the maximum number of rows to return, while [66 specifies the number of rows to skip before starting to return rows. When both are specified, [66 rows are skipped before starting to count the [65 rows to be returned. If the [65 expression evaluates to NULL, it is treated as [70, i.e., no limit. If [66 evaluates to NULL, it is treated the same as [72. SQL:2008 introduced a different syntax to achieve the same result, which PostgreSQL also supports. It is: WHERE 6In this syntax, the [66 or [65 value is required by the standard to be a literal constant, a parameter, or a variable name; as a PostgreSQL extension, other expressions are allowed, but will generally need to be enclosed in parentheses to avoid ambiguity. If [65 is omitted in a [76 clause, it defaults to 1. The [77 option is used to return any additional rows that tie for the last place in the result set according to the HAVING 04 clause; HAVING 04 is mandatory in this case, and [80 is not allowed. [81 and HAVING 33 as well as [83 and [84 are noise words that don't influence the effects of these clauses. According to the standard, the [85 clause must come before the [76 clause if both are present; but PostgreSQL is laxer and allows either order. When using WINDOW06, it is a good idea to use an HAVING 04 clause that constrains the result rows into a unique order. Otherwise you will get an unpredictable subset of the query's rows — you might be asking for the tenth through twentieth rows, but tenth through twentieth in what ordering? You don't know what ordering unless you specify HAVING 04.The query planner takes WINDOW06 into account when generating a query plan, so you are very likely to get different plans (yielding different row orders) depending on what you use for WINDOW06 and [85. Thus, using different WINDOW06/ [85 values to select different subsets of a query result will give inconsistent results unless you enforce a predictable result ordering with HAVING 04. This is not a bug; it is an inherent consequence of the fact that SQL does not promise to deliver the results of a query in any particular order unless HAVING 04 is used to constrain the order.It is even possible for repeated executions of the same WINDOW06 query to return different subsets of the rows of a table, if there is not an HAVING 04 to enforce selection of a deterministic subset. Again, this is not a bug; determinism of the results is simply not guaranteed in such a case.GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] 2 CommandThe command WHERE 7is equivalent to WHERE 8It can be used as a top-level command or as a space-saving syntax variant in parts of complex queries. Only the WHERE 9, HAVING 7, WINDOW65, WINDOW97, HAVING 04, WINDOW06, [85, [76 and { RANGE | ROWS | GROUPS } 08 locking clauses can be used with GROUP BY [ ALL | DISTINCT ] 2; the SELECT DISTINCT ON (location) location, time, report
FROM weather_reports
ORDER BY location, time DESC;
8 clause and any form of aggregation cannot be used.
What is a SELECT query in MS Access?A select query is a database object that shows information in Datasheet view. A query does not store data, it displays data that is stored in tables. A query can show data from one or more tables, from other queries, or from a combination of the two.
What type of query creates a new table using the query results?A make table query retrieves data from one or more tables, and then loads the result set into a new table. That new table can reside in the database that you have open, or you can create it in another database.
What can queries be used for?A query can give you an answer to a simple question, perform calculations, combine data from different tables, add, change, or delete data from a database. Since queries are so versatile, there are many types of queries and you would create a type of query based on the task.
What happens when you run a SELECT query in Access?You use select queries and crosstab queries to retrieve and present data, and to supply forms and reports with data. When you run a select or a crosstab query, Access displays the results in Datasheet view.
|
