CODEWITHSUNDEEP
sqlsqlite
David Nelband
David Nelband

Reputation: 427

order results from table A by total number of related rows form table B

i have two tables, "topics" (table A) and "topic votes" (table B). each "topic" has multiple "topic votes". i need to select rows from "topics" and order them by the total number of related "topic votes".

how is it possible in sqlite? do i need to create a view for this, or is it possible to solve with JOIN?

Upvotes: 1

Views: 28

Answers (2)

Gurwinder Singh
Gurwinder Singh

Reputation: 39517

Assuming you have a PK id in topics table and FK topic_id in topic votes table:

select
    t.*
from topics t
left join topic_votes v on t.id = v.topic_id
group by t.id
order by count(v.topic_id) desc;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

If you don't need the votes you can just put a correlated query in the order by:

select t.*
from topics t
order by (select count(*) from topic_votes tv where t.topic = v.topic) desc;

Normally, you would want the number of votes in the result set as well. A simple method is to move the subquery to the select clause:

select t.*,
       (select count(*) from topic_votes tv where t.topic = v.topic
       ) as votes
from topics t
order by votes desc;

Upvotes: 1

Related Questions