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.