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 :(