Finding the name of the supplier that supplies the most parts

Question

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.

I tried:

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.

Answer 1

You didn't specify your DBMS, so this is ANSI SQL:

select sid, 
       num_products
from (
  select sid, 
         count(*) as num_products,
         dense_rank() over (order by count(*) desc) as rnk
  from catalog
  group by sid
) t
where rnk = 1;

If you need the information from the supplier table as well, you need to join it:

select s.sname, 
       t.num_products
from (
  select sid, 
         count(*) as num_products,
         dense_rank() over (order by count(*) desc) as rnk
  from catalog
  group by sid
) t
  join suppliers s on s.sid = t.sid
where t.rnk = 1;

Online example: http://rextester.com/HGL10937

Answer 2

This really depends on the RDBMS you're using. For SQL Server it would be:

SELECT TOP 1 main.*
FROM (SELECT S.sid, COUNT(*) AS NUM
  FROM Catalog C, Suppliers S
  WHERE C.sid=S.sid
  GROUP BY S.sid) main
ORDER BY main.NUM DESC

With Oracle, it would be something like:

SELECT TOP 1 main.*
FROM (SELECT S.sid, COUNT(*) AS NUM
  FROM Catalog C, Suppliers S
  WHERE C.sid=S.sid
  GROUP BY S.sid
ORDER BY main.NUM DESC) main
WHERE ROWNUM = 1

The issue with both of these answers is that they do not take into account if two suppliers are "tied" for first. with SQL Server you'd use something like ROW_NUMBER or RANK to take care of that. Depends on your RDBMS and your specific needs.