[thelist] SQL- grouping and dates
rudy
r937 at interlog.com
Tue Apr 3 14:59:39 CDT 2001
> Note that the root and message column are the same
> for the original mesage posted in that thread.
>
>What I'm looking for is this result set:
>
>A (Subject A) has 4 messages, the most recent updated at 04/03/2001 13:13
>B (Subject B) has 3 messages, the most recent updated at 04/03/2001 11:05
select Root
, Subject
, (select count(Message) from yourTable X
where X.Root = Z.Root) as Messages
, (select max(Update) from yourTable Y
where Y.Root = Z.Root) as LastUpdate
from yourTable Z
where Root = Message
i think this is SQL-92 syntax, and you'd have to test it on your own
database to see if it will run
if this doesn't work, can you create a view?
create view RootStats as
select Root
, count(Message) as Messages
, max(Updated) as LastUpdate
from YourTable
group by Root
then just join the table to the grouped view
select yourTable.Root
, yourTable.Subject
, RootStats.Messages
, RootStats.LastUpdate
from yourTable, RootStats
where yourTable.Root = yourTable.Message
and yourTable.Root = RootStats.Root
rudy
More information about the thelist
mailing list