CODEWITHSUNDEEP
sqlsqlite
maregor
maregor

Reputation: 797

Show multiple MAX results

Suppose I have a table as follows:

loan:
no  | credit
-------------
L1  | 600
L2  | 550
L3  | 800
L4  | 800
L5  | 700

If I want to find the max loan I can simply do this:

SELECT MAX(l.no)
FROM loan l
WHERE l.credit = (SELECT MAX(l.credit) from loan l)

But this would only return me one loan. Is there a way to display both L3 and L4 without making a complicated query?

Upvotes: 0

Views: 42

Answers (3)

Gaurav Lad
Gaurav Lad

Reputation: 1808

Use Group by credit

Something like below

SELECT l.no FROM loan l WHERE l.credit = (SELECT MAX(l.credit) from loan l) Group by credit;

Upvotes: 0

User2012384
User2012384

Reputation: 4919

Why you put a "MAX" in the select query?

--delete the MAX here, or else only one record would be fetched.
SELECT l.no
FROM loan l
WHERE l.credit = (SELECT MAX(l.credit) from loan l)

Upvotes: 2

fancyPants
fancyPants

Reputation: 51868

Yes, just don't use an aggregate function without a group by if you want to have more than one row.

SELECT no, credit
FROM loan l
WHERE l.credit = (SELECT MAX(l.credit) from loan l)

Upvotes: 2

Related Questions