[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