Question 114104 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Returning the entity with max number of participants

I’m using pgsql to find the cage number (cno) that holds the largest number of animals but doesn’t have a bird in it.

The way I tried to do it is by creating a table that counts the number of animals in each cage (not including those with birds) and then return the ones where the count equals the max value.

select temp.cno,temp.size
from
(select cage.cno,cage.size,count(*) as q
from cage,animal
where cage.cno = animal.cno  and cage.cno not in (select cno from animal where lower(atype)='sheep')
group by cage.cno,cage.size) as temp
where temp.q = (select max(q) from temp)

I’m getting the following error message

ERROR:  relation "temp" does not exist
LINE 7: where temp.q = (select max(q) from temp)

Any idea how to overcome this issue? Why isn’t temp recognized within the last sub query?

Here are the tables

  1. cage (cno, type, size)

  2. animal (aid, aname, cno, atype)

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I solved it by ordering the results descending and using limit 1 to show the first row (which is the max)


  2. 0

    You already found out that a subquery defined in the FROM is not visible inside another subquery defined in the WHERE clause.

    This is easily solvable with the use of a CTE (with a proper join):

    WITH temp AS (
  SELECT c.cno, c.size, COUNT(*) AS q
  FROM cage c INNER JOIN animal a
  ON a.cno = c.cno  
  WHERE c.cno NOT IN (SELECT cno FROM animal WHERE LOWER(atype) = 'bird')
  GROUP BY c.cno, c.size
)
SELECT cno, size
FROM temp
WHERE q = (SELECT MAX(q) FROM temp);

    But, if there is a case that in a cage exist animals of more than one type then the condition:

    c.cno NOT IN (SELECT cno FROM animal WHERE LOWER(atype) = 'bird')

    is not correct, because it returns all cages which contain other types than birds without a restriction that there are only other types than birds.

    You can apply this restriction with aggregation.

    If you want/expect only 1 cage as result:

    SELECT c.cno, c.size
FROM cage c INNER JOIN animal a
ON a.cno = c.cno
GROUP BY c.cno
HAVING MAX((LOWER(a.atype) = 'bird')::int) = 0
ORDER BY COUNT(*) DESC LIMIT 1;

    If you want more than one cages with the largest number of animals, use RANK() window function:

    WITH cte AS (
  SELECT c.cno, c.size,
         RANK() OVER (ORDER BY COUNT(*) DESC) rnk
  FROM cage c INNER JOIN animal a
  ON a.cno = c.cno
  GROUP BY c.cno
  HAVING MAX((LOWER(a.atype) = 'bird')::int) = 0
)  
SELECT cno, size FROM cte WHERE rnk = 1;

    Note that since cno is the PRIMARY KEY of cage you only need to group by cno.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search