0

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

2
  • 1
    create a dbfiddle.uk (or similar) that demonstrates the problem and paste the URL into the question Jan 3, 2021 at 12:22
  • ty didn't know about dbfiddle yet but i very much like! Jan 3, 2021 at 18:42

1 Answer 1

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

5
  • right your answer sense.. how do I rank the results for each row in the parent query by a returned value in the subquery? (or any other technique besides a subquery).. it makes no sense to me Jan 3, 2021 at 7:42
  • i updated OP to try to make an improvement but still kind of stuck on the same concept Jan 3, 2021 at 7:46
  • Your edit adds in an additional parameter as well, which obviously changes the query entirely. It's not clear to me what you're trying to do. Can you explain in plain, conversational English (ie, a user story, rather than a code/pseudo-code) what your query should do?
    – AMtwo
    Jan 3, 2021 at 7:52
  • yeah will explain here and then update OP....all I want to do is get a subset from one table, ordered/ranked based off the result of a second table...to be more specific, it would be the count of matching rows in table 2, where those rows matched the row in table 1. seems like a common query type, just hard to explain in words. Jan 3, 2021 at 8:01
  • ok i updated OP with example data, ty pls let me know if it makes sense Jan 3, 2021 at 8:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.