This is Part 2 of the three part series detailing the ‘tricky SQL test’ I took to get a potential job.  Read Part 1 of the series here.  This one is much easier than Part 1.  I got most of the credit for this one, but I couldn’t figure out the group by to limit the number previous results.

The Test

Given a table events with the follow structure:

CREATE TABLE camera (
    cameraId INT NOT NULL,
    warningType INT NOT NULL,
    warningLevel INT NOT NULL,
    warningTime DATETIME NOT NULL,
    UNIQUE INDEX warningTime (warningTime)
);

insert into camera (cameraId, warningType, warningLevel, warningTime) values (2, 2, 8, '2017-09-13 13:42:00');
insert into camera (cameraId, warningType, warningLevel, warningTime) values (2, 4, -46, '2017-09-13 14:19:57');
insert into camera (cameraId, warningType, warningLevel, warningTime) values (2, 2, 7, '2017-09-13 15:48:30');
insert into camera (cameraId, warningType, warningLevel, warningTime) values (3, 2, 3, '2017-09-13 13:54:39');
insert into camera (cameraId, warningType, warningLevel, warningTime) values (2, 3, 38, '2017-09-13 14:32:36');

Write an SQL query that, for each camera and warning type (cameraId, warningType), returns the most recent value (in terms of time).  the table should be ordered by cameraId (in ascending order), and warningType (in ascending order).

For example, given the following data:

cameraId   | warningType | warningLevel | warningTime
-----------+-------------+--------------+--------------------
2          | 2           | 5            | 2017-09-13 13:42:00
2          | 4           | -46          | 2017-09-13 14:19:57
2          | 2           | 7            | 2017-09-13 15:48:30
3          | 2           | 3            | 2017-09-13 13:54:39
2          | 3           | 38           | 2017-09-13 14:32:36

Your query would return:

cameraId | warningType | warningLevel
---------+-------------+--------------
2        | 2           | 7
2        | 3           | 38
2        | 4           | -46
3        | 2           | 3

The names of the columns in the rowset don’t matter, but their order does.