Out of 2 tables, get the table with the most rows in DB2

Question

I have a DB2 database with tables and relations between animals. There are 2 tables, Monkey and Parrot. Monkey looks like this:

CREATE TYPE MonkeyType UNDER AnimalType AS
    (
    chimp CHAR(1)
    )
    MODE DB2SQL
    OVERRIDING METHOD food() RETURNS VARCHAR(10),
    OVERRIDING METHOD intelligence() RETURNS INT
    @

Parrot looks like this:

CREATE TYPE ParrotType UNDER AnimalType AS
    (
    numcolors INT
    )
    MODE DB2SQL
    OVERRIDING METHOD food() RETURNS VARCHAR(10),
    OVERRIDING METHOD intelligence() RETURNS INT
    @

Animal looks like this:

CREATE TYPE AnimalType AS
    (
    name VARCHAR(10),
    birthyear INT
    )
    INSTANTIABLE
    MODE DB2SQL
    METHOD food() RETURNS VARCHAR(10),
    METHOD intelligence() RETURNS INT
    @

DDL for the tables:

CREATE TABLE Animal OF AnimalType
        (REF IS Oid user GENERATED)@

CREATE TABLE Monkey OF MonkeyType UNDER Animal
    INHERIT SELECT PRIVILEGES@

CREATE TABLE Parrot OF ParrotType UNDER Animal
    INHERIT SELECT PRIVILEGES@

Every type of animal has it's own favorite food, monkeys have bananas and parrots have seeds.

I need a query that finds the food(s) eaten most frequently across parrots and monkeys. This is the same as asking which table has the most rows, parrot or monkey, and returning the favorite food(s) of the largest table.

I've tried the query:

SELECT * 
FROM (SELECT COUNT(*) AS monkey_count FROM Monkey), 
    (SELECT COUNT(*) AS parrot_count FROM Parrot)

which gives me:

MONKEY_COUNT PARROT_COUNT
------------ ------------
           5            2

But I don't know how to use this info to create the full query.

Answer 1

select temp.food 
from (    (select distinct Oid->food() AS food, (select count(*) from Monkey) as amount from Monkey) 
              union 
          (select distinct Oid->food() AS food, (select count(*) from Parrot) as amount from Parrot)) AS temp 
where temp.amount >= all 
                ((select count(*) from monkey) union (select count(*) from Parrot))