CODEWITHSUNDEEP
sql-serverselectjoin
sorax nana
sorax nana

Reputation: 21

SQL Server: Parent id with the least number 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.

Upvotes: 1

Views: 47

Answers (2)

Timeout
Timeout

Reputation: 7909

Now that you mentioned you're using SQL Server, 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.

Upvotes: 3

Jeff Foster
Jeff Foster

Reputation: 11

If you are working with Netezza, you could try: 

SELECT name_instructor, COUNT(id_client)
FROM instructor_table
    JOIN client_table on instructor_table.id_instructor = client_table.FK_instructor
GROUP BY name_instructor
ORDER BY COUNT(id_client) DESC
LIMIT 3

There is great documentation for Netezza here: http://www-304.ibm.com/support/knowledgecenter/SSULQD_7.2.0/com.ibm.nz.dbu.doc/c_dbuser_sql_grammar.html

There are also SQL tutorials here: http://www.w3schools.com/sql/

Upvotes: 1

Related Questions