Windowing / Partition By

[haydenflinner]

Windowing is a pretty common trick for a few things in SQL, I use it most often just to get the most recent sample for a particular key, though with CASE it can be extended to basically arbitrary orderings. I get a lot of syntax errors thrown when I try to use this, and I don't see it in the docs, so I'm guessing it's not implemented. This is probably a more advanced feature to come down the line, but it can probably be replicated in simple cases with 2 roundtrip queries on the client side, not too bad. Just filing it for the roadmap / search to bring up.

In mysql format, from a tutorial, the err:

MySQL [mydb]> select challenge_id, h_id, h_name, score, 
    ->    dense_rank() over ( partition by challenge_id order by score desc ) 
    ->        as "rank", from hacker;
ERROR 1105 (HY000): Code: 1005, displayText = error: 
  --> SQL:2:34
  |
1 | select challenge_id, h_id, h_name, score, 
  | ------ while parsing `SELECT ...`
2 |    dense_rank() over ( partition by challenge_id order by score desc ) 
  |    ----------        -           ^^ expected `)` or `,`
  |    |                 |            
  |    |                 while parsing `(<expr> [, ...])`
  |    while parsing expression

Postgres fmt for those more familiar:

WITH added_row_number AS (
  SELECT
    *,
    ROW_NUMBER() OVER(PARTITION BY mypkey_like
        ORDER BY my_timestamp DESC) AS row_number
  FROM mytable
)

SELECT
        mypkey_like, col1, col2, col3
FROM added_row_number
WHERE row_number = 1;

[youngsofun]

we used to support it，it was delete when we introduce the new planner，adding it back was blocked by the new expression.

it is now in our roadmap 2023，and I just take the issue #6342

[haydenflinner]

Thank you!!