Looking for guidance on another stupid max() query

[sptrsn]

Just when I thought I had this figured out, some combination of data comes up and whad'ya know? it doesn't work again.

Here is the data

 

id buyer apn                 bid         bidstatus

1 61         10101120 77000 ACTIVE

2 62         10101120 99000 CANCELLED

3 63         10101120 75000 ACTIVE

4 62         10115374 99000 ACTIVE

5 62         10206459 75000 ACTIVE

 

I want the highest "active" bid for each apn. With this data, that should be row 1, 4 and 5

I have tried everything I know and been searching for hours.

 

Here's what I have that doesn't work...

select id, buyer_id, apn, bid, bidstatus from prop p inner join
(select max(bid)b from prop group by apn)m  
on p.bid=m.b 
where bidstatus='active' 

 

Here is the result of that query...

 

id buyer_id apn bid bidstatus

3 63 10101120 75000 ACTIVE

4 62 10115374 99000 ACTIVE

4 62 10115374 99000 ACTIVE

5 62 10206459 75000 ACTIVE

 

Wrong in multiple ways. What the heck?

Can anyone help me with this?

 

[gizmola]

Your problem is that you are not having your inner group by include both the apn and the bid.  You also want to eliminate cancelled bids in the group by query.

 

select p.* from prop p inner join (select apn, max(bid) as bid from prop WHERE bidstatus = 'ACTIVE' GROUP BY apn) m on p.bid = m.bid AND p.apn = m.apn;

[reyborn]

Yes group the result.

[gizmola]

Yes group the result.

 

Was this supposed to add something to what I already provide (the actual query needed?)