0

Hi I have below query

SELECT
    CLUSTER_ID AS CLUSTER_ID,
    "date"
FROM
    HISTORY
GROUP BY
    CLUSTER_ID,
    "date"
ORDER BY
    CLUSTER_ID,
    "date"

with below output

1   2023-02-27
1   2023-03-01
1   2023-03-06
1   2023-03-13
1   2023-03-20
1   2023-03-27
1   2023-04-01
1   2023-04-03

when used above query as inner query as below, my expectation is to get last date which is 2023-04-03 but to my surprise it is giving first value which is 2023-02-27

SELECT
    LAST_VALUE(CLUSTER_ID),
    LAST_VALUE("date")
FROM
    (

        SELECT
            CLUSTER_ID AS CLUSTER_ID,
            "date"
        FROM
            HISTORY
        GROUP BY
            CLUSTER_ID,
            "date"
        ORDER BY
            CLUSTER_ID,
            "date")
    GROUP BY
        CLUSTER_ID

It is giving expected value 2023-04-03 when DESC order is used in inner query like below

SELECT
    LAST_VALUE(CLUSTER_ID),
    LAST_VALUE("date")
FROM
    (
    SELECT
        CLUSTER_ID AS CLUSTER_ID,
        "date"
    FROM
        HISTORY
    GROUP BY
        CLUSTER_ID,
        "date"
    ORDER BY
        CLUSTER_ID,
        "date" DESC)
GROUP BY
    CLUSTER_ID

can someone please explain the execution flow of the query

arkay
  • 149
  • 1
  • 12
  • Ordering in subqueries really won't matter, its sort of a waste of processing. the LAST_VALUE() function itself is where you would instruct it to use DESC ... LAST_VALUE(c) OVER(ORDER BY c DESC) – Josh Apr 18 '23 at 13:13
  • Thank you, but I see the ordering in sub query is impacting the output that too first is giving last of ordered result from inner query and vice versa, looking to understand execution flow – arkay Apr 18 '23 at 15:45
  • Can you compare and share the profiles of both queries? https://docs.exasol.com/db/latest/performance/profiling.htm – GriGrim Apr 20 '23 at 09:19

0 Answers0