ranked_messages, cte, row_number

WITH ranked_messages AS (
  SELECT m.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) AS rn
  FROM messages AS m
)
SELECT * FROM ranked_messages WHERE rn = 1;

/* last row by group */
WITH ranked_messages AS (
  SELECT b.*, ROW_NUMBER() OVER (PARTITION BY i ORDER BY ts DESC) AS rn
  FROM e0407 a JOIN rdns2 b USING(i)
)
SELECT * FROM ranked_messages -- WHERE rn = 1;



/* last 10 rows by group */

WITH ranked_messages AS (
  SELECT b.*, ROW_NUMBER() OVER (PARTITION BY i ORDER BY ts DESC) AS rn
  FROM e0407 a JOIN tab2 b USING(i)
)
SELECT * FROM ranked_messages  WHERE rn <=10;