I have this table:
| ID | ParentNumber | ObjectNumber | Start | Component | … |
|---|---|---|---|---|---|
| 4b8cf664-0ef9-44b2-a266-6c27079b90ed | 2 | 1 | 2023-08-10T12:50:03.716000Z | 1 | … |
| 92847bf9-dbfc-46ff-a9bb-b2210df66507 | 2 | 1 | 2023-08-11T17:13:30.716000Z | 1 | … |
| fa9e0823-4432-480f-bbff-fbdac16a53fe | 2 | 1 | 2023-08-10T13:06:06.716000Z | 2 | … |
| 9f3d4140-d8cc-4e01-8221-0991d2cc0ea4 | 2 | 1 | 2023-08-10T10:45:03.716000Z | 3 | … |
| 02b70a62-77b7-4133-83a7-6c76d6cf7204 | 2 | 1 | 2023-08-12T09:26:22.716000Z | 3 | … |
| 0cb48763-8bc1-4a10-8be4-721c8644dd94 | 2 | 1 | 2023-08-14T08:12:42.716000Z | 3 | … |
I want to select rows where ParentNumber = 2 & ObjectNumber = 1.
The hard part is that I want to select the latest entry distinct by Component. So I want to only get those rows:
| ID | ParentNumber | ObjectNumber | Start | Component | … |
|---|---|---|---|---|---|
| 92847bf9-dbfc-46ff-a9bb-b2210df66507 | 2 | 1 | 2023-08-11T17:13:30.716000Z | 1 | … |
| fa9e0823-4432-480f-bbff-fbdac16a53fe | 2 | 1 | 2023-08-10T13:06:06.716000Z | 2 | … |
| 0cb48763-8bc1-4a10-8be4-721c8644dd94 | 2 | 1 | 2023-08-14T08:12:42.716000Z | 3 | … |
I tryed to group by Component but with that I have the problem that I can’t get all the other informations of those rows…
How can I get those rows?
>Solution :
You can do this by first obtaining the most recent start for each component and then joining it to the table.
select m.*
from mytable m
inner join (
select Component, max(Start) as latest_Start
from mytable
where ParentNumber = 2 and ObjectNumber = 1
group by Component
) as l on l.Component = m.Component and l.latest_Start = m.Start