[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:
SUBJ:
Root Subject
-------------
abc Subject A
rgh Subject B
and MESSAGES
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
FROM (
SELECT root, count(message) as count, max(updated) as updated
FROM messages
GROUP BY root
) a, subj b
WHERE a.root = b.root
Oren
-----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
http://www.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