PostgreSQL query for returning sorted table entries by one-to-many children count-CodePudding

I have a table posts which has a one-to-many relationship with comments.

Table Posts

id | text
---------
1  | "foo"
2  | "bar"
3  | "baz"

Table Comments

id | post_id | text
-------------------
1  | 2       | "lorem"
2  | 2       | "ipsum"
3  | 3       | "dolor"

I want to compose a query that will return all the posts entries sorted by highest to lowest of the related comments count

So my output should be:

Table Posts

id | text
---------
2  | "bar"    (2 Total comments)
3  | "baz"    (1 Total comments)
1  | "foo"    (0 Total comments)

CodePudding user response：

select p.id, min(p.text) as text
from posts p left outer join comments c on c.post_id = p.id
group by p.id
order by count(c.post_id) desc;

This one might not be portable or fully supported:

select * from posts p
order by (select count(*) from comments c where c.post_id = p.id) desc;

And I tested this one as well for fun:

select distinct on (count(c.post_id) over (partition by p.id), p.id) p.id, p.text
from posts p left outer join comments c on c.post_id = p.id
order by count(c.post_id) over (partition by p.id) desc, p.id

https://dbfiddle.uk/?rdbms=postgres_13&fiddle=6e4a54dd00cfe45267b678d912654c25