There is a table with the url and created_at fields.

There are many entries in the table with the same url . I need to get entries with the most senior created_at , for each group with the same url . Ie, having:

 url | created_at ------------------------ abc | 1 abc | 2 def | 3 def | 0

Receive:

 url | created_at ------------------------ abc | 2 def | 3

Tried to do it like this:

 SELECT * FROM items GROUP BY url HAVING created_at = MAX(created_at)

I receive:

 ERROR: column "items.id" must appear in the GROUP BY clause or be used in an aggregate function

I tried the same thing a little differently:

 SELECT MAX(created_at) AS last_created_At, * FROM items GROUP BY url HAVING created_at = last_created_at

Received:

 ERROR: column "last_created_at" does not exist LINE 4: HAVING created_at = last_created_at`

Looks like I misunderstand how GROUP BY HAVING works. How do I get what I want?

ADDED: Reached the desired with the help of a subquery, but the question remains open, how to do it without him:

 SELECT * FROM items WHERE created_at = (SELECT MAX(created_at) FROM items AS t WHERE t.url = items.url)
  • if the memory has not disappeared, then so SELECT url, MAX (created_at)
  • Besides url and created_at, there are other fields, I need SELECT * . If you add * to your request, it will return entries that I don’t need, just by adding a maximum, without filtering. - Vladimir Gordeev

3 answers 3

Here's a discussion: https://stackoverflow.com/a/7745635/276994 If I understood correctly: you need either a subquery or self-join.

  • Yes, it seems easier way. - Vladimir Gordeev
 SELECT url,created_at FROM items GROUP BY url ORDER BY created_at DESC
  • Please try to write more detailed answers. I am sure the author of the question would be grateful for your expert commentary on the code above. - Nicolas Chabanovsky ♦

Because url identical, then you just need to find the maximum created_at :

 SELECT url, MAX(created_at) as created_at FROM items GROUP BY url