CODEWITHSUNDEEP
sql
dannash918
dannash918

Reputation: 454

SQL - How to list cells with value greater then another cell?

I have the following database, and I need to list all subno, subname where the quota is greater than the quota of subno 30012.

subno   subname                 quota
30006   Adv Database design 300
30007   Software fundamentals   200
30008   Application Development 350
30010   Database development    300
30011   System design           200
30012   Requirement engineering 350

I know I can do

select subno, subname from subject
where quota > 350

but how do I change this to allow for the quota to be greater than 30012's quota rather than greater than 350?

Upvotes: 2

Views: 1413

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269943

You use a subquery:

select subno, subname from subject
where quota > (select quota from subject where subno = 30012)

This assumes that there is only one possible quote for a given subno. If more than one is possible, then use an aggregation function, such as:

select subno, subname
from subject
where quota > (select max(quota) from subject where subno = 30012)

Upvotes: 5

Related Questions