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.
Advertisement
Answer
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