How can I select the highest post per topic and associate them given this schema design?

Question

I am working on a small forum component for a site and I am creating a page where I want to display each topic along with its highest rated answer. Here are what the tables look like:

POST       USER      TOPIC
id         id        id 
date       name      title   
text       bio       date
views                
likes
topic_id
author_id

My query looks like so:

select 
      u.id, u.name, u.bio, 
      p.id, p.date, p.text, p.views, p.likes, 
      t.id, t.title, t.date
 from
      ( select p.id, max(p.likes) as likes, p.topic_id 
        from post as p group by p.topic_id ) as q 

      inner join post as p on q.id = p.id
      inner join topic as t on t.id = q.topic_id 
      inner join user as u on u.id = p.author_id

 order by date desc;

One of the problems I'm having running this is withing "q". Postgresql wont let me run the "q" query because it wants "p.id" to be in the "group by" clause or in an aggregate function. I tried to use "distinct on (p.id)" but I got the same error message: p.id must appear in the GROUP BY clause or be used in an aggregate function.

Without the p.id attribute, I cannot meaningfully link it to the other tables; is there another way of accomplishing this?

what if their are ties do you want both to be returned or just 1 per topic? window functions such as DENSE_RANK or ROW_NUMBER will get you what you want — Matt, Jun 22 '16 at 16:50

score 1 · Accepted Answer · answered Jun 22 '16 at 16:54

;WITH cte AS (
    SELECT
       u.id AS UserId
       ,u.name
       ,u.bio
       ,p.id AS PostId
       ,p.[date] AS PostDate
       ,p.text
       ,p.views
       ,p.Likes
       ,t.id AS TopidId
       ,t.title
       ,t.[date] AS TopicDate
       ,p.Likes
       ,ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY p.Likes DESC, p.[date] DESC) AS RowNum
       ,DENSE_RANK() OVER (PARTITION BY t.id ORDER BY p.Likes DESC) AS RankNum
    FROM
       topic t
       INNER JOIN post p
       ON t.id = p.topic_id
       INNER JOIN [user] u
       ON p.author_id = u.id
)

SELECT *
FROM
    cte
WHERE
    RowNum = 1

;

switch RowNum to RankNum if you want to see ties for most liked

score 1 · Answer 2 · edited May 23 '17 at 11:58

This is a common need: when grouping, show each group's first/last a ranked by some other criteria b. I don't have a name for it, but this seems to be the canonical question. You can see there are a lot of choices! My favorite solution is probably a lateral join:

SELECT  u.id, u.name, u.bio,
        p.id, p.date, p.text, p.views, p.likes,
        t.id, t.title, t.date
FROM    topic t
LEFT OUTER JOIN LATERAL (
  SELECT  *
  FROM    post
  WHERE   post.topic_id = t.id
  ORDER BY post.likes DESC
  LIMIT 1
) p
ON true
LEFT OUTER JOIN "user" u
ON  p.author_id = u.id
;

score 1 · Answer 3 · answered Jun 22 '16 at 17:35

SELECT
  u.id AS uid, u.name, u.bio 
  , p.id AS pid, p."date" AS pdate, p.text, p.views, p.likes
  , t.id AS tid, t.title, t."date" AS tdate
FROM post p  
JOIN topic t ON t.id = p.topic_id 
JOIN user u ON u.id = p.author_id
WHERE NOT EXISTS ( SELECT *
    FROM post nx
    WHERE nx.topic_id = p.topic_id
    AND nx.likes > p.likes)
ORDER BY p."date" DESC
    ;

How can I select the highest post per topic and associate them given this schema design?

3 Answers3