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 – Lennart Jan 3 at 12:22
  • ty didn't know about dbfiddle yet but i very much like! – Alexander Mills Jan 3 at 18:42
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 – Alexander Mills Jan 3 at 7:42
  • i updated OP to try to make an improvement but still kind of stuck on the same concept – Alexander Mills Jan 3 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 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. – Alexander Mills Jan 3 at 8:01
  • ok i updated OP with example data, ty pls let me know if it makes sense – Alexander Mills Jan 3 at 8:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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