SELECT DISTINCT ON

Is your feature request related to a problem?

Let's say I have a table that logs alerts:

CREATE TABLE IF NOT EXISTS app_alerts (
        ts          TIMESTAMP,
        priority    INT,
        message     STRING
    )
    timestamp(ts)
    PARTITION BY DAY;

And I want to get the X most recent rows while de-duplicating any repeated messages.

This query:

SELECT DISTINCT message FROM app_alerts ORDER BY ts DESC LIMIT 50;

yields:

ERROR:  ORDER BY expressions must appear in select list. Invalid column: ts
LINE 1: SELECT DISTINCT message FROM app_alerts ORDER BY ts DESC LIM...
                                                         ^

Describe the solution you'd like.

I'm thinking support for something like PostgreSQL's DISTINCT ON (expression) could be employed? e.g.,

SELECT DISTINCT ON (message)
       ts, message, priority FROM app_alerts ORDER BY ts DESC LIMIT 50;

Describe alternatives you've considered.

IDK if it's semantically equivalent but I'm using:

SELECT ts, message, priority FROM app_alerts LATEST ON ts PARTITION BY message ORDER BY ts DESC LIMIT 50;

Additional context.

No response

Start by reviewing QuestDB's existing LATEST ON syntax and the SQL parsing and execution paths for DISTINCT, ORDER BY, and LIMIT. Compare the requested DISTINCT ON behavior with the PostgreSQL example and existing alternative, then define tests showing that de-duplicated messages return the selected rows in timestamp order and respect the limit.