CODEWITHSUNDEEP
mysqlsqlmysql-5.7
Milad
Milad

Reputation: 338

Compare rows in one table with MySQL

I tried to compare rows in one table that I have. The table is like this:

table

id - fid - tp - ct

11 - 222 - 1 - 1
12 - 222 - 2 - 0
13 - 222 - 3 - 0

14 - 333 - 1 - 3
15 - 333 - 2 - 2
16 - 333 - 3 - 0

17 - 555 - 1 - 2
18 - 444 - 1 - 0
19 - 555 - 2 - 0
20 - 444 - 2 - 0
21 - 444 - 3 - 0
22 - 555 - 3 - 0

I have 3 rows for comparing, and they had tp 1,2,3 for each one with the same fid and different id. The question is, how can I compare three of them to return?

For example,

if (t1 == 0){ // check t1
   return t1;
}else if (t1 > 0 AND t2 == 0){ // check t2
   return t2;
}else if (t1 > 0 AND t2 > 0 AND t3 == 0){ // check t3
   return t3;
}

more explanation

For example, if ct of the tp 1 is equal to 0 so we return it, and tp 2 should compare with tp 1 if ct of the tp 1 is larger than 0 so we can return tp 2. if ct of the tp 2 and tp 1 is larger than 0 return tp 3. (all of them have the same fid)

the result should be like this:

===================
id | fid | tp | ct
-------------------
12 | 222 | 2  | 0
-------------------
16 | 333 | 3  | 0
-------------------
18 | 444 | 1  | 0
-------------------
19 | 555 | 2  | 0
===================

I can handle this part without SQL. I can return all rows and compare them and return the result that I want, but It's not a good solution, Because I want to handle all this part just with MySQL.

Upvotes: 1

Views: 106

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You seem to want the first time that ct = 0 for each fid based on the id. That suggests:

select t.*
from (select t.*,
             row_number() over (partition by fid order by id) as seqnum
      from t
      where ct = 0
     ) t
where seqnum = 1;

EDIT:

In older versions of MySQL, you can use:

select t.*
from (select t.*,
             (select min(t2.id)
              from t t2
              where t2.ct = 0 and t2.fid = t.fid
             ) as min_id
      from t
      where ct = 0
     ) t
where id = min_id

Upvotes: 1

Popeye
Popeye

Reputation: 35900

You can use not exists as follows:

Select t.* from your_table t
 Where not exists
       (Select 1 from your__table tt
         Where tt.fid = t.fid and tt.tp < t.tp and tt.ct = 0)
  And t.ct = 0

Upvotes: 0

Related Questions