In my query I need to pick the highest value out of the two rows that the filter is 3.
isbn ⎪ quantity1 ⎪ bid ⎪ filter
⎪ ⎪ ⎪
abcd ⎪ 1 ⎪ 112.59 ⎪ 3
⎪ ⎪ ⎪
abcd ⎪ 1 ⎪ 107.16 ⎪ 3
⎪ ⎪ ⎪
klmn ⎪ 1 ⎪ 130.4 ⎪ 1
⎪ ⎪ ⎪
pqrs ⎪ 1 ⎪ 142 ⎪ 2
this is what I want
isbn ⎪ quantity1 ⎪ bid ⎪ filter
⎪ ⎪ ⎪
abcd ⎪ 1 ⎪ 112.59 ⎪ 3
⎪ ⎪ ⎪
klmn ⎪ 1 ⎪ 130.4 ⎪ 1
⎪ ⎪ ⎪
pqrs ⎪ 1 ⎪ 142 ⎪ 2
I tried this but nothing changes I still get two rows of filter = 3.
SELECT isbn, quantity1, filter, CASE WHEN filter = 3 THEN max(bid) ELSE bid END as bid
FROM tb1
*rest of the query*
GROUP BY isbn, quantity1, bid, filter