On 3 Nov 2005 08:41:10 -0800, pb648174 wrote:
In the below structure, if I wanted to get the Id of the comment for
each Generic record having the latest comment time, how would I do that
not using a subquery?
Table: Generic
Id
Description
Table: Comment
Id
GenericId
CommentTime
Currently I have something like the following:
Select
Generic.Id, Max(Comment.CommentTime) /*,Comment.Id for max comment
time comment record*/
From
Generic
INNER JOIN Comment ON Generic.Id = Comment.GenericId
Group By
Generic.Id
To get it, I could do a sub query, using the above query as its source
and joining on the max comment time, but I was wondering if there was a
way to do it without a sub query. Keep in mind that I am looking for a
set of Generic records and not looking for only a single record (so
select top top 1 with order by won't work)
Hi pb648174,
You can do this in two ways.
1. Using a correlated subquery (probably the solution you already had in
mind, since you write: "if there was a way to do it without a sub
query", but I'll give it anyway)
SELECT g.Id, c.CommentTime, c.Id
FROM Generic AS g
INNER JOIN Comment AS c
ON c.GenericId = g.Id
WHERE c.CommentTime = (SELECT MAX(c2.CommentTime)
FROM Comment AS c2
WHERE c2.GenericId = c.GenericId)
2. Using a derived table. This is a subquery as well, but it's not
correlated, and it's used in the FROM clause, in place of a table or
view name:
SELECT g.Id, c.CommentTime, c.Id
FROM Generic AS g
INNER JOIN (SELECT GenericId, MAX(CommentTime) AS MaxCommentTime
FROM Comment
GROUP BY GenericId) AS c2
ON c2.GenericId = g.Id
INNER JOIN Comment AS c
ON c.GenericId = g.Id
AND c.CommentTime = c2.MaxCommentTime
(Note: both queries are untested - see
www.aspfaq.com/5006 if you prefer
a tested reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)