[thelist] SQL- grouping and dates

Oren Levin lists at pinetree.net
Tue Apr 3 14:45:59 CDT 2001

If you split this into 2 tables:

Root  Subject
abc   Subject A
rgh   Subject B

Root   Message    Updated
abc    abc       04/01/2001 04:00
rgh    rgh       04/01/2001 06:03
abc    gha       04/01/2001 12:00
abc    1jr       04/02/2001 06:03
rgh    jus       04/03/2001 04:10
rgh    jiu       04/03/2001 11:05
abc    qwe       04/03/2001 13:13

You can then do an inline select (in Oracle, not sure if mySQL let's you do
this yet):

SELECT a.root, a.count, a.updated, b.subj
  SELECT root, count(message) as count, max(updated) as updated
  FROM messages
	GROUP BY root
) a, subj b
WHERE a.root = b.root


-----Original Message-----
From: thelist-admin at lists.evolt.org
[mailto:thelist-admin at lists.evolt.org]On Behalf Of Chris Evans
Sent: Tuesday, April 03, 2001 3:30 PM
To: thelist at lists.evolt.org
Subject: RE: [thelist] SQL- grouping and dates

Here's a little better example of a thread I want to pull back:

Root   Message    Updated             Subject
abc    abc       04/01/2001 04:00    Subject A
rgh    rgh       04/01/2001 06:03    Subject B
abc    gha       04/01/2001 12:00    RE: Subject A
abc    1jr       04/02/2001 06:03    RE: Subject A
rgh    jus       04/03/2001 04:10    RE: subject B
rgh    jiu       04/03/2001 11:05    RE: Subject B
abc    qwe       04/03/2001 13:13    Changed sbject, same thread

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

Pulling the subject for the original message posted in the thread, but
getting the date of the last message posted in the thread is where I'm
getting hung up.

Chris Evans
chris at fuseware.com

For unsubscribe and other options, including
the Tip Harvester and archive of TheList go to:
http://lists.evolt.org Workers of the Web, evolt !

More information about the thelist mailing list