You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
I have a fairly expensive Rel8 select of the form SELECT * FROM ... LIMIT n OFFSET k. (I'll elide the OFFSET, since it doesn't change anything below.)
In addition to fetching a subset of the rows using a LIMIT, I'd like to also fetch the total number of qualifying rows, which in SQL I would do as follows:
SELECT*, COUNT(*) OVER ()
FROM ...
LIMIT n
I'm not sure how to do COUNT(*) OVER () in Rel8 in the first place. Window appears to be for window function-based queries, not windows in the WINDOW w AS ... sense, and Partitions can only be used to modify Windows as far as I can tell from the API.
Given that there doesn't seem to be any support for non-MATERIALIZED CTEs either, I'm also not sure how to express this query in the usual alternative manner where one puts the query in a CTE and then selects from it twice (WITH cte AS (...) SELECT * FROM (SELECT count(*) FROM cte) LEFT JOIN (TABLE cte ORDER BY ... LIMIT n) ON true).
I have a fairly expensive Rel8 select of the form
SELECT * FROM ... LIMIT n OFFSET k
. (I'll elide theOFFSET
, since it doesn't change anything below.)In addition to fetching a subset of the rows using a
LIMIT
, I'd like to also fetch the total number of qualifying rows, which in SQL I would do as follows:I'm not sure how to do
COUNT(*) OVER ()
in Rel8 in the first place.Window
appears to be for window function-based queries, not windows in theWINDOW w AS ...
sense, andPartition
s can only be used to modifyWindow
s as far as I can tell from the API.Given that there doesn't seem to be any support for non-
MATERIALIZED
CTEs either, I'm also not sure how to express this query in the usual alternative manner where one puts the query in a CTE and then selects from it twice (WITH cte AS (...) SELECT * FROM (SELECT count(*) FROM cte) LEFT JOIN (TABLE cte ORDER BY ... LIMIT n) ON true
).Certainly I could do
and I suspect this will produce the right output, but I'm not sure it's idiomatic for Rel8.
The text was updated successfully, but these errors were encountered: