SQL experts here

[growse]

Right, I'm trying to do this query on a database. I've got two tables, parent and child. Each parent row has an id, a datestamp, and a few other things on it. Each child entry also has an id, a datestamp, other stuff and the id of the parent it's tied to. So there can be many children tied to a parent.

What I need to do, is to select every parent sorted by the datestamp desc of the most recent comment tied to that parent. If there is no comment, it should use the datestamp of the parent.

I've been playing around with max(datestamp) and left joins, but can't for the life of me get this to work. I'm using MSSQL, so the limit command goes right out the window.

Any ideas?

 

[growse]

Genious, did'nt know about the isnull function. Learn something new every day

Now I just have to implement that statement with the stupid way that you have to do pagination on MSSQL, and it'll be there

 

[growse]

I've just come across a fault in the sql I was trying to implement on this statement. I had the statement:

select
e.id, isnull(max(c.datestamp),e.datestamp), e.subject as title,e.user_id as poster,c.user_id,count(*)
from entries e
left outer join comments c
on c.entry_id = e.id
where blog_id=36
group by e.id, e.datestamp,e.subject,e.user_id,c.user_id
order by isnull(max(c.datestamp), e.datestamp) desc

The problem is that if the latest comment's user_id is different to the poster's user_id, I get two rows returned for that entry when I only want the one that returns the latest comment's user_id. If they're the same value, then the group by statement means that only one row is displayed. The only fix I've got is to fetch the comment.user_id separately for each row, but this is a bit wasteful. Any ideas?