Tricky SQL Test: Part 2

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.

How to change keyboard shortcuts in Oracle SQL Developer

It is quite easy to assign keyboard shortcuts to actions in SQL Developer.  I recently configured shortcuts to convert text to all UPPER and all lower case.

Simply go to Tools → Preferences and navigate to Shortcut Keys.  On the right there will be a lengthy list of actions to choose from.  For converting to upper/lower case look in the Code Editor category for Convert Selection to Lower/Uppercase.