SQL SELECT WHERE Problem in using any ideas
I have two tables parent and child (related both on PK / FK GUID)
The child has a time stamp (date / time when the entry was created).
I only want to make the most recent child and parent record, FOR EACH parent record.
SELECT
dbo_Parents.ParentName,
dbo_ChildEntry.CountPropertys,
dbo_ChildEntry.DateTimeStamp
FROM
dbo_Parents INNER JOIN dbo_ChildEntry
ON
dbo_Parents.ParentID = dbo_ChildEntry.ParentID
WHERE
([WHAT SHOULD BE HERE?]))
0
a source to share
5 answers
Assuming you want the most recent entry, you should use TOP 1 and order by.
SELECT TOP 1
dbo_Parents.ParentName,
dbo_ChildEntry.CountPropertys,
dbo_ChildEntry.DateTimeStamp
FROM dbo_Parents
INNER JOIN dbo_ChildEntry ON dbo_Parents.ParentID = dbo_ChildEntry.ParentID
ORDER BY dbo_ChildEntry.DateTimeStamp desc
Edit after clarification: "most recent child record and parent record" FOR EACH parent record ":
WHERE dbo_ChildEntry.DateTimeStamp =
( Select Max( dbo_ChildEntry.DateTimeStamp )
from dbo_ChildEntry
where dbo_Parents.ParentID = dbo_ChildEntry.ParentId )
+8
a source to share
select p.ParentName,
c.CountPropertys,
c.DateTimeStamp
from (
select max(DateTimeStamp) as DateTimeStamp, ParentID
from dbo_ChildEntry
group by ParentID) cm
inner join dbo_ChildEntry c on cm.DateTimeStamp = c.DateTimeStamp and cm.ParentID = c.ParentID
inner join dbo_Parents p on c.ParentID = p.ParentID
+1
a source to share
Since you figured out that you need one row for each parent, try this. For each row, it looks for a child row with a later timestamp. In the WHERE clause, it filters rows that do not have a "next" child.
SELECT
dbo_Parents.ParentName,
cur.CountPropertys,
cur.DateTimeStamp
FROM dbo_Parents
INNER JOIN dbo_ChildEntry cur
ON dbo_Parents.ParentID = cur.ParentID
LEFT JOIN dbo_ChildEntry next
ON dbo_Parents.ParentID = next.ParentID
AND next.DateTimeStamp > cur.DateTimeStamp
WHERE
next.DateTimeStamp is null
0
a source to share