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.
x
-------------------------------------------------------
| 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
Advertisement
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.