CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
user5620472
user5620472

Reputation: 2882

select from tbl where tbl.field is min

I have select

SELECT e.*, b.f1, b.fee FROM eq e JOIN br b ON e.country = b.country
WHERE e.name= 'some' AND b.fee = 1

I need rewrite select an replace AND b.fee = 1 to AND b.fee = min fee from b

I can not....I can AND b.fee = (SELECT MIN(fee)FROM br) But it bad solution

Upvotes: 0

Views: 119

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

If you want only one row, then just use limit:

SELECT e.*, b.f1, b.fee
FROM eq e JOIN
     br b
     ON e.country = b.country
WHERE e.name = 'some'
ORDER BY b.fee
LIMIT 1;

If you want all rows with the minimum free, then here is one method:

SELECT e.*, b.f1, b.fee
FROM eq e JOIN
     (SELECT b.*, MIN(fee) OVER () as minfee
      FROM br b
     ) b
     ON e.country = b.country
WHERE e.name = 'some' AND b.fee = minfee;

Upvotes: 2

Related Questions