Geek Answers Handbook

What does PARTITION BY 1 mean?

For a pair of cursors, where the total number of rows in the result set is required immediately after the first FETCH (after some trial and error), I came up with the request 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 that the query output is X rows, rows_in_result accurately reflects this.

  • What does PARTITION BY 1 mean?
    • I think this probably tells the database to split the results into pieces of 1 row each
+4
source share
2 answers

This is an unusual use of PARTITION BY. What he does is put everything in one section, so if the query returns 123 rows in total, then the rows_in_result value for each row will be 123 (as follows from its alias).

Therefore, it is equivalent to shorter ones:

 COUNT(*) OVER ()
+9
source

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

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

Note that Sybase SQL Anywhere and CUBRID interpret this 1 as a reference to the column index, similar to what is possible in the ORDER BY [...] . This may seem a little surprising, as it imposes an evaluation order on the query projection. In your case, this would mean the following:

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

This curious deviation from the interpretation of other databases allows you to refer to more complex grouping expressions.

+1
source

More articles:


All Articles