using count from subquery results to order parent query results

Question

I have this query:

       select * from mbk_file
       where user_id = $1
       order by (
           select count(file_id) from mbk_file_label 
           where file_id = mbk_file.id
       ) DESC
   
  

I noticed that changing DESC to ASC didnt change the order, even though I would expect it should. So that tells me that the subquery is not really doing anything useful.

I have these tables:

-----mbk_file------
id   bigint


-----mbk_file_label-----
id   bigint
file_id  (from above table)

where there is a 1 to many between the 1st and 2nd tables. I don't think I need a group by clause, I just need a count of all the rows where file_id is from the 1st table?

I think I need to do something like this instead:

         select *, (
                 select count(file_id) from mbk_file_label 
                 where file_id = mbk_file.id
           ) as xxx
           from mbk_file
           where user_id = $1
           order by xxx DESC

but I still don't understand how file_id will vary by row..

let me explain with data:

---mbk_file----   // has 3 records total
1  
2
3


---mbk_file_label
1  1   
1  2
1  3
1  4       // file 1 has 4 matching records 

2  1       
2  3       // file 2 has 2 matching records

3  2       
3  3
3  4       // file 3 has 3 matching records

so what I want to do is rank the mbk-files, based off of how many matching records there are in mbk-file-label.

So the rank/order for select result for mbk-files would be:

1
3
2

since 1, has 4 matching records, file 2 has 2 matching records and file 3 has 3 matching records. I hope this makes sense because it's pretty simple but i don't know how to create the query fml :(

Answer 1

Your ORDER BY is this:

order by (
           select count(file_id)....
       ) DESC

The count will return a (constant) integer, such as 300. Which means you are effectively saying ORDER BY 300 DESC (where 300 is the number of file_id values for the specified user_id). This isn't using a column to sort, but a constant.

Sorting by a constant does nothing. You presumably expect your results to be ordered based on a value in a column (or a calculation derived from a column), but that's not what the query does