Postgresql – using count from subquery results to order parent query results

postgresql

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 🙁

Your ORDER BY is this:
order by (

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.