[dmitryminkovsky]

I've been working on a messaging app, and at the beginning I started out with Elasticsearch. Now, ES is really a wonderful tool and it got me pretty far, but its lack of window functions made one particular requirement impossible that we all take for granted in messengers: a listing of most-recently updated threads represented by the most recent message on each thread, sorted reverse chronologically. Here is the SQL that makes that happen:

  SELECT
    tm.*
  FROM
    (
      SELECT
        row_number() OVER (
          PARTITION BY tm_1.thread_id
          ORDER BY tm_1.delivered_at DESC
        ) AS "row",
        tm_1.*
      FROM
        thread_message tm_1
    ) tm
  WHERE
    tm."row" = 1;

The inner query groups all messages by thread, orders them to find the "most recent" message on a thread given my ordering requirements, and then assigns a row number to each such message such that I can pick the most recent message on each thread in the outside query. I still have no idea how I would have done this with Elasticsearch.