[thelist] SQL- grouping and dates
Chris Evans
chris at fuseware.com
Tue Apr 3 12:46:46 CDT 2001
Oren,
This worked, thanks. Now, a related question. I also want to grab the
message id of the MIN(updated) record. So based on my earlier example, my
result set would look like this:
A (abc) has 4 messages, the most recent updated at 04/03/2001 13:13
B (rgh) has 3 messages, the most recent updated at 04/03/2001 11:05
Is it possible to do it in the same query? It is easy enough to add a
second query to pull these back, but if you have a large record set, these
extra queries could add up.
Chris Evans
chris at fuseware.com
http://www.fuseware.com
-----Original Message-----
From: thelist-admin at lists.evolt.org
[mailto:thelist-admin at lists.evolt.org]On Behalf Of Oren Levin
Sent: Tuesday, April 03, 2001 12:23 PM
To: thelist at lists.evolt.org
Subject: RE: [thelist] SQL- grouping and dates
How about:
SELECT root, count(message), max(updated)
FROM ???
GROUP BY 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 11:56 AM
To: thelist at lists.evolt.org
Subject: RE: [thelist] SQL- grouping and dates
oh, and ideally, I would like the result set ordered by the LastUpdated
column.
Chris Evans
chris at fuseware.com
http://www.fuseware.com
-----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 11:37 AM
To: thelist at lists.evolt.org
Subject: [thelist] SQL- grouping and dates
I have a SQL problem that seems like it should be easy, but it is driving me
crazy. Here is a sample of the data:
Root Message Updated
--------------------------
A abc 04/01/2001 04:00
B rgh 04/01/2001 06:03
A gha 04/01/2001 12:00
A 1jr 04/02/2001 06:03
B jus 04/03/2001 04:10
B jiu 04/03/2001 11:05
A qwe 04/03/2001 13:13
The result I want is:
A has 4 messages, the most recent updated at 04/03/2001 13:13
B has 3 messages, the most recent updated at 04/03/2001 11:05
I can get the counts with no problem, but picking up the LastUpdated date is
giving me headaches.
Suggestions?
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 !
---------------------------------------
For unsubscribe and other options, including
the Tip Harvester and archive of TheList go to:
http://lists.evolt.org Workers of the Web, evolt !
---------------------------------------
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