Stack Exchange
SQL SELECT WHERE Trouble any ideas helpful
I have two tables parent and child (related as such on PK/FK GUID)
Child has a Timestamp (the record creation date/time).
What I want to do is get only the most recent child record AND the 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?]))


You have to define "most recent".

I would like to select only the child row with the most recent datetimestamp (ie which ever record contains the timestamp closest to Now())

Do you expect one result row per parent, or one result in total?

I did a poor job of saying this initially, I want one result (most recent child + parent) per parent row.

Assuming that you want the most recent entry, you have to 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: "the most recent child record AND the 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 )


My bad -- I want should have been more clear (question edited). I want to return the most recent child and its parent FOR EACH parent.

WHERE dbo.ChildEntry.DateTimeStamp = ( Select Max( c.DateTimeStamp )
                                         from dbo.ChildEntry c
                                        where dbo_Parents.ParentID = c.ParentId )


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


Since you clarified you'd like one row per parent, try this.  For every row, it searches for a child row that has a later timestamp.  In the WHERE clause, it filters on 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


Try
SELECT     dbo_Parents.ParentName,     dbo_ChildEntry.CountPropertys,     
max(dbo_ChildEntry.DateTimeStamp)
FROM     dbo_Parents 
INNER JOIN dbo_ChildEntry ON    dbo_Parents.ParentID = dbo_ChildEntry.ParentID
group by dbo_Parents.ParentName,     dbo_ChildEntry.CountPropertys


-1 You're grouping on dbo_ChildEntry.CountPropertys, so max(datetimestamp) only finds the maximum timestamp for children with the same CountPropertyes.
