Get top two rows per group efficiently Currently, I use a query with DISTINCT ON to get a list of recent posts for each author.
But how can I get previous recent posts for each author? In other words, how to make DISTINCT ON return second row of each group, not first.
I need this to compare recent post with previous one.
CREATE TABLE posts (
  title varchar(30),
  author varchar(30),
  created_at date
);

INSERT INTO posts VALUES
('Johns first post', 'John', 'January 1, 2021'),
('Johns second post', 'John', 'January 2, 2021'),
('Johns third post', 'John', 'January 3, 2021'),
('Mikes first post', 'Mike', 'January 1, 2021'),
('Mikes second post', 'Mike', 'January 2, 2021'),
('Mikes third post', 'Mike', 'January 3, 2021');

This query selects the most recent post for each author. (Third post for each in my example.):
SELECT DISTINCT ON (author) * FROM posts ORDER BY author ASC, created_at DESC

db&lt;&gt;fiddle here
I also need the previous post for each author (Second post for each in my example.)
I don't want to use window functions because my table is large enough, and I suppose window functions could be slow.
You can use ROW_NUMBER window function to set an order, then simply use it as a subquery with a proper predicate.
SELECT FROM
(
  Subquery
)
WHERE 
    post_num &lt;= 2;



SELECT
    title,
    author,
    created_at,
    ROW_NUMBER() OVER (PARTITION BY author ORDER BY created_at) as post_num
FROM
    posts


title             | author | created_at | post_num
:---------------- | :----- | :--------- | -------:
Johns first post  | John   | 2021-01-01 |        1
Johns second post | John   | 2021-01-02 |        2
Johns third post  | John   | 2021-01-03 |        3
Mikes first post  | Mike   | 2021-01-01 |        1
Mikes second post | Mike   | 2021-01-02 |        2
Mikes third post  | Mike   | 2021-01-03 |        3


db&lt;&gt;fiddle here