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 🙁

Best Answer

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

Related Solutions

Postgresql – Formulating a Join Query for PostqreSQL

Assuming (video_id, user_id) is unique in user_video, a plain LEFT JOIN would do the job:

SELECT v.*, uv.video_id IS NOT NULL AS has_bought
FROM   video v
LEFT   JOIN user_video uv ON uv.video_id = v.video_id
                         AND uv.user_id = $current_user_id;

$current_user_id being the ID of the current user.

If (video_id, user_id) is not unique, you could add GROUP BY

SELECT v.*, count(uv.video_id) > 0 AS has_bought
FROM   video v
LEFT   JOIN user_video uv ON uv.video_id = v.video_id
                         AND uv.user_id = $current_user_id
GROUP  BY v.video_id;

count() doesn't count NULL values.
Or use an EXISTS semi-join to avoid duplicates:

SELECT v.*, EXISTS (SELECT 1 FROM user_video uv 
                    WHERE  uv.video_id = v.video_id
                    AND    uv.user_id = $current_user_id) AS has_bought
FROM   video v;

Or use the PostgreSQL-specific DISTINCT ON, that's particularly handy if you need the result in a certain sort order anyway.

SELECT DISTINCT ON (v.video_id)
       v.*, uv.video_id IS NOT NULL AS has_bought
FROM   video v
LEFT   JOIN user_video uv ON uv.video_id = v.video_id
                         AND uv.user_id = $current_user_id
ORDER  BY v.video_id, uv.video_id;  -- NULL sorts last

Postgresql – Sort results of a query by the order of OR clause

There are two ways to do this in Postgres:

Join your table against a virtual table build from a values list:

with names (idx, name) as (
  values 
    (1, 'example.co.uk'), 
    (2, 'co.uk'), 
    (3, 'uk')
)
select s.*
from suffixes s
  join names n on n.name = s.name
order by n.idx
limit 1;

Another shorter version would be to join against an array:

select s.*
from suffixes s
  join unnest(array['example.co.uk', 'uk', 'co.uk']) with ordinality as n(name, idx) 
    on n.name = s.name
order by n.idx
limit 1

The second version has the advantage, that you don't need to supply the additional index because that's implicitly defined through the order of the values inside the array.

Best Answer

Related Solutions

Postgresql – Formulating a Join Query for PostqreSQL

Postgresql – Sort results of a query by the order of OR clause

Related Question