[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


More information about the thelist mailing list