I have the following schema:
Suppliers(sid: integer, sname: string, address: string) Parts(pid: integer, pname: string, color: string) Catalog(sid: integer, pid: integer, cost: real)
Now what I'm trying to do is get the name of the supplier that has the most items for sale in the Catalog. This is an example instance:
What I have so far is:
SELECT S.sid, COUNT(*) AS NUM FROM Catalog C, Suppliers S WHERE C.sid=S.sid GROUP BY S.sid
Which returns each supplier id and the number of parts they supply, which sounds like a good start; however, I'm not sure where to go from there.
SELECT Y.sid, MAX(Y.Num) FROM (SELECT S.sid, COUNT(*) AS NUM FROM Catalog C, Suppliers S WHERE C.sid=S.sid GROUP BY S.sid) Y
But that returned:
| sid | MAX(Y.NUM) | +-----+------------+ | 1 | 5 |
...which is wrong.
I really feel like I'm over complicating this and that there is a simple solution that I'm missing. Any help is appreciated.