Search code examples
javamysqlsqlresultset

SQL complex selection

I have to figure out a very complex selection. No idea how this is possible, but I think it is.

There are two tables:

threads: primary key is the column 'thread_id'

subscribes: primary key is 'user' together with 'thread_id'

I need a selection that selects all threads ordered by how many people have subscribed to them.

Example situation:

There are threads with the ids 1 - 5 in the table 'threads'

Subscribe table looks like this:

user     thread_id
x        1
x        2
y        2
z        4
z        2

You can see, most subscribed thread is 2. I want a selection that brings thread_id 2 first in its result, because 2 is the most subscribed one.

So can anyone help me? I tried for an hour but didn't find a solution

Solution 
  • SELECT thread_id, COUNT(*) AS cpt FROM subscribes GROUP BY thread_id ORDER BY cpt