DBA.SE Q #191637, MySQL

Language: Layout:

#MySQL 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existence before creating
#'\\' is a delimiter

CREATE TABLE IF NOT EXISTS the_table
(
  ID int,
  app varchar(4),
  env varchar(4),
  time_end varchar(23)
);
    
INSERT INTO
  the_table (ID, app, env, time_end)
VALUES
  (1, 'app1', 'env1', '2017-11-14 14:45:07.037'),
  (2, 'app1', 'env2', '2017-08-01 08:30:03.951'),
  (3, 'app1', 'env1', '2017-10-05 21:36:22.716'),
  (4, 'app2', 'env2', '2017-05-18 11:26:58.122'),
  (5, 'app1', 'env1', '2017-09-26 00:35:37.914'),
  (6, 'app1', 'env3', '2017-11-05 20:27:00.762'),
  (7, 'app1', 'env3', '2017-06-04 16:55:19.089'),
  (8, 'app3', 'env3', '2017-10-05 04:36:00.820'),
  (9, 'app3', 'env3', '2017-09-22 04:11:51.602'),
  (10, 'app2', 'env1', '2017-10-09 15:36:38.642'),
  (11, 'app1', 'env2', '2017-10-04 16:07:48.272'),
  (12, 'app1', 'env1', '2017-09-16 15:35:22.239'),
  (13, 'app1', 'env1', '2017-10-12 19:53:29.434'),
  (14, 'app1', 'env1', '2017-08-23 07:07:56.983')
;

SELECT
  t.*
FROM
  the_table AS t
  INNER JOIN
  (
    SELECT
      app,
      env,
      MAX(time_end) AS last_time_end
    FROM
      the_table
    GROUP BY
      app,
      env
  ) AS s ON t.app = s.app
        AND t.env = s.env
        AND t.time_end = s.last_time_end
;
\\
SELECT
  derived.*
FROM
  (
    SELECT
      @rank := (app = @app AND env = @env) * @rank + 1 AS rank,
      ID,
      @app := app AS app,
      @env := env AS env,
      time_end
    FROM
      (SELECT @app := '', @env := '', @rank := 0) AS x,
      the_table
    ORDER BY
      app ASC,
      env ASC,
      time_end DESC
  ) AS derived
WHERE
  derived.rank = 1
;
\\
DROP TABLE the_table;


absolute service time: 0,33 sec