[Info-ingres] Why can't I leave my window function ajar?

[Martin Bowes]

Hi All,

II 11.0.0 (a64.lnx/100) + p15426

It appears the first N feature isn't working with windowing functions like row_number() over ()

This query was supposed to select the first 1000 rows from the derived table ...so 12050912 was a bit of a shock.

DECLARE GLOBAL TEMPORARY TABLE session.my_gp_clinical_p1 AS
SELECT FIRST 1000 s.* FROM (
    SELECT *,
        row_number() over (
            partition by reg_id, data_provider, event_dt, read_2, read_3, map_icd10, value1, value2, value3
        ) as row_number
    FROM actual_gp_clinical
    WHERE mod(reg_id, 10) = 0
    ) s
WHERE s.row_number = 1
ON COMMIT PRESERVE ROWS WITH NORECOVERY, PAGE_SIZE = 32768
Executing . . .

(12050912 rows in 452.269470 secs)

Martin Bowes
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20190906/cd97e32d/attachment.html>