enigmq enigmq - 2 months ago 7
SQL Question

Select rows which ocured in other specific table as foreign key the most

I have this table for Operator:

+--------------------------------+
| ProviderID | ProviderName |
+---------------+----------------+
| 1 | Movistar |
+---------------+----------------+
| 2 | Vadafone |
+---------------+----------------+
| 3 | Orange |
+---------------+----------------+


And also I have this table of Plans those Operators provide. (simplified version):

+--------------------------------+
| PlanID | ProviderID |
+---------------+----------------+
| 1 | 2 |
+---------------+----------------+
| 2 | 2 |
+---------------+----------------+
| 3 | 2 |
+---------------+----------------+
| 4 | 1 |
+---------------+----------------+
| 5 | 3 |
+---------------+----------------+


I would like to print only those Providers who has the most Plans. This is what I have so far:

SELECT O.ProviderId, COUNT(Pl.ProviderId) as numberOfPlans
FROM Plan Pl
INNER JOIN Provider O ON O.ProviderId= P.ProviderId
GROUP BY O.ProviderName


And this prints all the provider names and number of plans they have while I need to print only those that has the most plans, so in this case it would be only Vadafone which has 3 plans. (If other provider had same maximum value of plans it would be printed out as well).
Sorry for question, didn't know how exactly I should specify the problem.

Answer

If you are using SQL Server you can use TOP. For instance, to get one of the providers with the most plans:

SELECT TOP 1 pl.ProviderId, COUNT(*) as numberOfPlans
FROM Plan pl
GROUP BY pl.ProviderId
ORDER BY numberOfPlans DESC;

In other databases, this would usually be:

SELECT pl.ProviderId, COUNT(*) as numberOfPlans
FROM Plan pl
GROUP BY pl.ProviderId
ORDER BY numberOfPlans DESC
FETCH FIRST 1 ROW ONLY;

or:

SELECT pl.ProviderId, COUNT(*) as numberOfPlans
FROM Plan pl
GROUP BY pl.ProviderId
ORDER BY numberOfPlans DESC
LIMIT 1;

If there are ties, then in SQL Server you can do:

SELECT TOP (1) WITH TIES pl.ProviderId, COUNT(*) as numberOfPlans
FROM Plan pl
GROUP BY pl.ProviderId
ORDER BY numberOfPlans DESC;

Notes:

  • You don't need the JOIN to the Provider table, because the ProviderId is in the Plan table.
  • The GROUP BY condition should be the same as the column in the SELECT.