What does PARTITION BY 1 mean?

For a pair of cursors where the total number of rows in the resultset is required immediately after the first FETCH, ( after some trial-and-error ) I came up with the query below

SELECT
 col_a,
 col_b,
 col_c,
 COUNT(*) OVER( PARTITION BY 1 ) AS rows_in_result
FROM 
 myTable JOIN theirTable ON
 myTable.col_a = theirTab开发者_开发问答le.col_z
GROUP BY
 col_a, col_b, col_c
ORDER BY 
 col_b

Now when the output of the query is X rows, rows_in_result reflects this accurately.

• What does PARTITION BY 1 mean?
  • I think it probably tells the database to partition the results into pieces of 1-row each

It is an unusual use of PARTITION BY. What it does is put everything into the same partition so that if the query returns 123 rows altogether, then the value of rows_in_result on each row will be 123 (as its alias implies).

It is therefore equivalent to the more concise:

COUNT(*) OVER ()

Databases are quite free to add restrictions to the OVER() clause. Sometimes, either PARTITION BY [...] and/or ORDER BY [...] are mandatory clauses, depending on the aggregate function. PARTITION BY 1 may just be a dummy clause used for syntax integrity. The following two are usually equivalent:

[aggregate function] OVER ()
[aggregate function] OVER (PARTITION BY 1)

Note, though, that Sybase SQL Anywhere and CUBRID interpret this 1 as being a column index reference, similar to what is possible in the ORDER BY [...] clause. This might appear to be a bit surprising as it imposes an evaluation order to the query's projection. In your case, this would then mean that the following are equivalent

COUNT(*) OVER (PARTITION BY 1)
COUNT(*) OVER (PARTITION BY col_a)

This curious deviation from other databases' interpretation allows for referencing more complex grouping expressions.