`sort | take` before an aggregation should enforce the sort in the CTE

[lukapeschke]

What happened?

Having a sort | take before a group should not erase the sort step in the resulting CTE, as the result of the SQL query is not equal: With the given input, the expected result should be an aggregation by network of the 7 highest values in the Total column. The actual result we get is an aggregation of the 7 first values

PRQL input

from my_table
sort {-this.`Total`} | take 7
group { this.`network` } ( aggregate { `total_sum_by_network` = sum this.`Total` } )
sort {-this.`total_sum_by_network`}

SQL output

WITH table_0 AS (
  SELECT
    network,
    "Total"
  FROM
    my_table
  LIMIT
    7
)
SELECT
  network,
  COALESCE(SUM("Total"), 0) AS total_sum_by_network
FROM
  table_0
GROUP BY
  network
ORDER BY
  total_sum_by_network DESC

Expected SQL output

WITH table_0 AS (
  SELECT
    network,
    "Total"
  FROM
    my_table
  ORDER BY "Total" DESC # This should be added
  LIMIT
    7
)
SELECT
  network,
  COALESCE(SUM("Total"), 0) AS total_sum_by_network
FROM
  table_0
GROUP BY
  network
ORDER BY
  total_sum_by_network DESC

MVCE confirmation

• Minimal example
• New issue

Anything else?

No response