0

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:

enter image description here

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.

enter image description here

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.

2

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

1

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.

  • row_number() or rank() are available in Oracle as well (in fact way longer then they are available in SQL Server) – a_horse_with_no_name Dec 16 '16 at 14:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.