sorax nana sorax nana - 4 years ago 99
SQL Question

SQLServer: Parent id with the least amount of children

I have two tables

Client
and
Instructor
:

Client table :

id_client|name_client|FK_instructor
---------+-----------+------------
1 | Clinton | 2
2 | Gates` | 1
3 | Bush | 1
4 | Clinton | 2
5 | Obama | 1
6 | Jack | 3


Instructor table :

id_instructor|name_instructor
-------------+---------------
1 | Sara
2 | Sam
3 | Dean
4 | Julie
5 | Jake


I want to select the 3 instructors who have the least number of clients associated.

Thank you in advance.

Answer Source

Now that you mentioned you're using SQLServer, in addition to the GROUP BY and ORDER BY you need a TOP(3) on your SELECT.

SELECT   TOP(3) i.id_instructor, i.name_instructor
FROM     Instructor i
JOIN     Client c ON c.FK_instructor = i.id_instructor
GROUP BY i.id_instructor, i.name_instructor
ORDER BY COUNT(*) --Implicitly ascending

Note that I added the instructor id to the group by compared to the other answer in case more than one instructor has the same name.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download