Mysql - Filtering out rows without inner joins

Question

(I don't know how to title this question) I have a table say t1 with 3 fields.

 f1----f2----f3
 1     v1     0
 2     v2     0
 3     v3     0
 4     v2    10
 5     v3    10

I want to pick up values from the tables if f3 = 0 or f3 = 10. However, if there is record with f3 = 10, then it would override 0. To make it easier to understand,

Q1: select * from t1 where f3 = 0  => returns (1,v1,0), (2,v2,0), (3,v3, 0)
Q2: select * from t1 where f3 = 10 => returns           (2,v2,10),(3,v3,10)

What I want is (1,v1,0), (2,v2,10), (3,v3,10). I want to do this with some simple if statement in the where condition. Is this possible?

if you `select ... f3=10`, then it'd be impossible to return any `f3=0` rows... — Marc B, May 07 '14 at 15:15
you want to filter down to the rows with `f3 = 0 or 10`, and then return the row for each `v2` with the highest `f3`, right? — Barmar, May 07 '14 at 15:17
do you need the pk for a specific reason or can you do without? — serakfalcon, May 07 '14 at 15:17
If that's what you want, see http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group — Barmar, May 07 '14 at 15:18
@Marc B: Ofcourse, specifying f3=0 wouldn't fetch any 10s. What I wish to do, is to combine the two queries and make a where condition with some IF statements and achieve the expected result. — Ram, May 07 '14 at 16:25
What if there's no corresponding f3 = 0 record for a f3 = 10 record? — Marcus Adams, May 07 '14 at 17:13

score 0 · Answer 1 · answered May 07 '14 at 15:19

0

You should try something like:

 Select f1, f2, MAX(f3) from t1
 where f3 IN (0, 10)
 group by f2;

answered May 07 '14 at 15:19

Marshall Tigerus

3,675
10
37
67

Using `MAX()` on f3 only, you won't have the correct values for the f1 column. – Marcus Adams May 07 '14 at 17:09
the results woudl require a MIN(f1) but that really makes zero sense since it is a key – Marshall Tigerus May 07 '14 at 17:10

Mysql - Filtering out rows without inner joins

1 Answers1