CODEWITHSUNDEEP
sqlsqlite
maregor
maregor

Reputation: 797

Select multiple minimum values

I want to show the loans which are borrowed the least. This is a case where two loans are tied in the smallest number of borrowers.

Loan:
no | type    
------------
L1 | house  
L2 | student
L3 | car    

Borrower ('num' is foreign key to 'no'):
name | num
----------
A    | L1
A    | L1
A    | L2
A    | L3

SELECT loan.type
FROM loan, borrower
WHERE loan.no = b.num
  AND loan.type = (SELECT MIN(type) from loan)
  GROUP BY loan.type

This should produce student and car but for now it's just giving me one of them. How should I modify my query to solve this?

Upvotes: 1

Views: 1468

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Here is a method using a CTE:

WITH lb as (
      SELECT l.type, COUNT(b.num) as cnt
      FROM loan l LEFT JOIN
           borrower b
           ON l.no = b.num
      GROUP BY l.type
     )
SELECT lb.type
FROM lb
WHERE lb.cnt = (SELECT MIN(lb.cnt) FROM lb);

Notes:

  • If you want the loan types with the fewest borrowers, then you should be comparing the count, not the type.
  • The LEFT JOIN considers loan types with no borrowers.
  • You need to learn proper explicit JOIN syntax. Simple rule: Never use commas in the FROM clause; always use explicit JOIN syntax.

Upvotes: 3

Related Questions