News for hackers. Stuff that matters.
Re:Postgres 11 – A First Look by twic on Saturday September 22, 2018 @09:06AM (#18045821)
Given:

    create table conversations (
      message_id integer primary key,
      conversation_id integer,
      timestamp timestamp,
      user_id integer,
      message varchar
    );
Then:

    select distinct on (conversation_id) *
    from conversations
    order by conversation_id, timestamp desc;
Should do it. Although i believe "select distinct on" is a PostgreSQL extension rather than standard SQL.

Now, what i don't know is how this is planned for a large table, or what indices would let you get a sensible plan. Maybe just an index on (conversation_id, timestamp)?

Re:Postgres 11 – A First Look by tfehring on Sunday September 23, 2018 @12:47AM (#18049453)
Extension or not, that's a really useful shortcut. Coming from SQL Server, the best option I know of requires a CTE or subquery:

    WITH CTE_messages AS
    (
        SELECT
             *
            ,row_nbr = ROW_NUMBER() OVER(PARTITION BY conversation_id
                                         ORDER BY timestamp DESC)
        FROM
            conversations
    )
    SELECT
        *
    FROM
        CTE_messages
    WHERE    row_nbr = 1
    ORDER BY
        conversation_id
This does give a performant plan in SQL Server with an index on (conversation_id, timestamp), for what it's worth. I imagine the same is true for this syntax in PostgreSQL. But SELECT DISTINCT sometimes does strange things to execution plans in general, and I wouldn't be surprised if that extends to SELECT DISTINCT ON.