What does PARTITION BY 1 mean?

Question

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 = theirTable.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

Answer 1

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 ()