Feature Request: first with order_by in Python API

[MarcoGorelli]

I would like to have

duckdb.Expression('first', expr, order_by=order_by)

available in the Python API

Currently, I'm doing something like

duckdb.SQLExpression(f'first({expr} order by {order_by})')

In duckdb/duckdb#19252, I was suggested to use FunctionExpression. However, there doesn't seem to be a way to pass the order_by clause:

In [8]: rel = duckdb.sql("""
   ...: from values (1, 4, 2), (2, 5, 0), (3, 6, 1) df(a, b, i)
   ...: select *
   ...: """)

In [9]: rel.aggregate([duckdb.FunctionExpression('first', 'a')])
Out[9]:
┌────────────┐
│ "first"(a) │
│   int32    │
├────────────┤
│          1 │
└────────────┘

In [10]: rel.aggregate([duckdb.FunctionExpression('first', 'a', 'i')])  # how to pass "order by i" here?
---------------------------------------------------------------------------
BinderException                           Traceback (most recent call last)
Cell In[10], line 1
----> 1 rel.aggregate([duckdb.FunctionExpression('first', 'a', 'i')])

BinderException: Binder Error: No function matches the given name and argument types 'first(INTEGER, INTEGER)'. You might need to add explicit type casts.
        Candidate functions:
        first(DECIMAL) -> DECIMAL
        first(ANY) -> ANY

whereas with SQLExpression it works:

In [12]: rel.aggregate([duckdb.SQLExpression(f'first(a order by i)')])
Out[12]:
┌───────────────────────┐
│ "first"(a ORDER BY i) │
│         int32         │
├───────────────────────┤
│                     2 │
└───────────────────────┘

[Tishj]

.order(..) or .sort(..) adds the ORDER BY if memory serves

Hmm, aggregates might be a special case, especially with the order by on its arguments

[MarcoGorelli]

thanks, I've tried but get

In [13]: rel.aggregate([duckdb.FunctionExpression('first', duckdb.ColumnExpression('a').sort('i'))])
---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
Cell In[13], line 1
----> 1 rel.aggregate([duckdb.FunctionExpression('first', duckdb.ColumnExpression('a').sort('i'))])

AttributeError: 'duckdb.duckdb.Expression' object has no attribute 'sort'

same for order_by