I have a simple database that contains apartments in one table and the ids of the people interested in those apartments (or their ids) in another. I'm attempting to fetch the apartments with the most people interested in them using a nested query
select toh.t, toh.o, toh.h
from (
select ok.houseid as t, va.houseaddress as o, count ( ok.customerid ) as h
from house va
inner join is_interested ok
on va.houseid = ok.houseid
group by t
) toh
group by toh.t
having toh.h = max( toh.h )
;
which doesn't do what I want it to do. The inner select is supposed to fetch me a table with the IDs, addresses and finally the counts of the IDs of the people interested in them, grouped by apartment ID, which id does admirably.
Therefore the problem most likely lies with the last two lines:
group by toh.t
having toh.h = max( toh.h )
as it returns all of the apartments available, regardless of how many people are interested in them. Changing this to
group by toh.t
having toh.h > 1
selects the correct apartments for me (there are at most 2 people interested in said apartments at the moment). It looks like I don't fully understand how the function max is supposed to work. Shouldn't count return an integer, or is this related to type mismatches at all in the first place? Because it sure looks like it does.
You can't return the maximum that you want like that.
Use a CTE to get all the counts from is_interested and then join to house:
with
cte as (
select houseid, count(customerid) counter
from is_interested
group by houseid
)
select h.houseid, h.houseaddress, c.counter
from house h inner join (
select * from cte
where counter = (select max(counter) from cte)
) c on c.houseid = h.houseid