vandad vandad - 2 months ago 11
MySQL Question

MySql get row with max revision from 2 different column

This is my Table:

id dl_id rev1 rev2
1 48 1 0
2 48 1 1
3 50 0 2
4 50 1 2
5 50 2 1


This is wanted Result:

id dl_id
2 48
4 50


rev2 has more priority than rev1 , so I want id for dl_id with max revison.

this is my query :

select distinct dl_id,
(select id from myTable
where dl_id=m.dl_id
order by rev2 desc,rev1 desc limit 0,1) as id
from myTable m


my query is too slow , its took 4 seconds to run.
I need a better query

Answer

E.g (and note the indexes):

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,dl_id INT NOT NULL
,rev1 INT NOT NULL 
,rev2 INT NOT NULL
,UNIQUE(dl_id,rev1,rev2)
);

INSERT INTO my_table VALUES
(1    ,48    ,1    ,0),
(2    ,48    ,1    ,1),
(3    ,50    ,0    ,2),
(4    ,50    ,1    ,2),
(5    ,50    ,2    ,1);

SELECT x.*
  FROM my_table x
  JOIN
     ( SELECT a.dl_id
            , a.rev2
            , MAX(a.rev1) rev1
         FROM my_table a
         JOIN 
            ( SELECT dl_id
                   , MAX(rev2) rev2 
                FROM my_table 
               GROUP 
                  BY dl_id
            ) b
           ON b.dl_id = a.dl_id 
          AND b.rev2 = a.rev2
        GROUP
           BY a.dl_id
            , a.rev2
     ) y
    ON y.dl_id = x.dl_id
   AND y.rev2 = x.rev2
   AND y.rev1 = x.rev1;

+----+-------+------+------+
| id | dl_id | rev1 | rev2 |
+----+-------+------+------+
|  2 |    48 |    1 |    1 |
|  4 |    50 |    1 |    2 |
+----+-------+------+------+