0

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
Elif
  • 21
  • 5

0 Answers0