Hello.

Situation : There is a hypothetical table of posts containing information about publications. In it we are interested in the fields id (post id), author_id (author id), date (creation date).

Task : Select for each author the id of the last post. It would seem trivial.

Solution :

select author_id, max(date) from posts group by author_id

We get the latest posts for each author. This is not enough, id posts are needed.

If you do this:

 select author_id, id, max(date) from posts group by author_id

DBMS swears that id should be processed by an aggregate function, or referred to in GROUP BY .

The first option does not suit us, and the second one starts to ignore the grouping by the author id and returns all the rows from the table.

I would like to use this solution:

 select distinct on (author_id) author_id, id, max(date) from posts group by author_id, id

But even though its output is similar to the real one, I have doubts as to whether id and date belong to the same line in the final output (this is a critical request requirement). I made several test samples, joining the result on the same table and checking the dates from the first sample and from the table - everything seems to be ok, but doubts do not leave.

Actually the question is : Is it possible in the above solution to believe that the DBMS will return all the id pairs, date assigned to one line, i.e. does not pull the id from one and date from the other line of the table

Is there any proven solution to this problem? I think it’s pretty standard.

  • With a subquery did not try to make? - Rams666
  • 2
    For example: select author_id, id, date from posts where (author_id, date) in (select author_id, max (date) from posts group by author_id) - Rams666
  • And if he does not return a few lines for one author, if several posts were made within the same date? Plus, for clarity, I hid some of the conditions for selecting posts, which in your version will have to be duplicated in both queries. But the option is good, thanks. - ikoolik
  • > if several posts were made on the same date? Keep the time of publication. - msi

1 answer 1

Window functions

 SELECT author_id, id, date FROM (SELECT author_id, id, date, rank() OVER (PARTITION BY author_id ORDER BY date DESC) FROM posts) AS posts WHERE rank = 1;
  • What is not a day is a discovery. Thank. - ikoolik
  • As I understand it, in the variant from the question, there is no guarantee of compliance with id date '? - ikoolik