aggregate question

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)You need to take what you have and use it as a subquery as you've
suggested. Other than the subquery, there's no way in SQL to say "give
me the id column from the right table where some other column in the
right table has it's max value".

The reason you can't do that is simple: what id would you get back
from the right table if the maximum value occured in more than one row?|||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)|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you had done this right and realized tht ther are
no magical, universal "id' things in RDBMS, would the schema look like
this?

CREATE TABLE Generic
(generic_id INTEGER NOT NULL PRIMARY KEY,
description VARCHAR(30) NOT NULL,
..);

CREATE TABLE Comments
(generic_id INTEGER NOT NULL
REFERENCES Generic (generic_id)
comment_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
-- where is the comment??
PRIMARY KEY (generic_id, comment_time));

>> but I was wondering if there was a way to do it without a subquery. <<

No, not unless you move comment time into Gerneric.|||Thanks, Celko. Helpful and on-topic as always.