Get a group of records by last unknown id

I have a log table where I want to select the entries for a specific group of records that are grouped by an id I don’t know.
See the table below, there are more columns but these are not relevant for the question.

-------------------------------------------------------
| id | details    | deviceId | processId | timestamp  |
-------------------------------------------------------
| 1  | startup    | 753      | 538       | 1546346190 |
| 2  | processing | 753      | 538       | 1546346192 |
| 3  | logging    | 753      | 538       | 1546346217 |
| 4  | done       | 753      | 538       | 1546346220 |
| 5  | startup    | 567      | 539       | 1590648690 |
| 6  | processing | 567      | 539       | 1590648692 |
| 7  | logging    | 567      | 539       | 1590648717 |
| 8  | done       | 567      | 539       | 1590648720 |
| 9  | startup    | 753      | 540       | 1590655888 |
| 10 | processing | 753      | 540       | 1590655890 |
| 11 | logging    | 753      | 540       | 1590655915 |
| 12 | email sent | 753      | 540       | 1590655918 |
| 13 | done       | 753      | 540       | 1590655920 |
-------------------------------------------------------

I want to get the last entries of one process for one device
When I query something like this I get all the records of the device:

SELECT *
FROM logtable
WHERE deviceId = 753
ORDER BY timestamp DESC

How can I only get the records with processId 540 in one query when I don’t know the processId is 540?

I already tried it with a subquery:

SELECT *
FROM logtable
WHERE processId IN (
   SELECT processId
   FROM logtable
   WHERE deviceId = 753
   ORDER BY timestamp DESC
   LIMIT 1
)

But MariaDB doesn’t accept LIMIT in a subquery

Answer

Use =:

SELECT l.*
FROM logtable l
WHERE processId = (SELECT l2.processId
                   FROM logtable l2
                   WHERE l2.deviceId = 753
                   ORDER BY l2.timestamp DESC
                   LIMIT 1
                  );

You only need IN if the subquery is going to return multiple rows.

Advertisement

Answer