October 1, 2007 at 8:03 am
I have a table containing identifying information from eMail that I want to group/return by threads to display in a heirarchial grid.
Assuming a table schema of
(
EmailID guid,
ClientID guid,
DateSent datetime,
Subject varchar(255),
ThreadID guid
)
I need to return two tables within a dataset.
First datatable contains all records where the DateSent is the MAX value per ThreadID.
Second datatable would be all child records of the first - ie those with a matching ThreadID to a record in the first datatable, but not the 'latest' (not MAX(DateSent) for that ThreadID)
Schema can be changed if there is a better way to structure this.
Any suggestions?
October 1, 2007 at 8:22 am
What's the structure mean? Is EmailID somehow related to threadID or are you just ordering by the threadID.
Why not just order by threadid desc?
October 1, 2007 at 8:25 am
EmailID is the PK in the table.
I need to return all of the columns in the query
I need to return two separate DataTables from the query into an ADO.Net dataset to populate the grid.
October 1, 2007 at 8:27 am
October 1, 2007 at 8:29 am
October 1, 2007 at 8:32 am
Is the DateSent alone enough to get always maximum 1 row for a ThreadID, or can there be any ties (several rows with the same threadID and DateSent)? If there can be ties, do you wish to return all such rows, or just one for each ThreadID? This is not apparent from what you posted and needs to be solved first.
Do you want to return all threads, or just some of them (active during last x days, or whatever the timeframe maybe)?
Should it really be MAX, and not MIN? Usually the post/mail that starts a thread is the oldest one, the rest are replies to it... it somehow makes more sense than to use the newest one as identifier.
Once you have the first table, it is pretty easy to return all rows that are not in that table, using LEFT JOIN and IS NULL construct.
October 1, 2007 at 8:37 am
bledu (10/1/2007)
but as for the sql, the plain old select should do the trick as suggested by Steve above.
As I understand it, ThreadID is a FK in this table, and the same can appear several times... but only the "last" row (or a couple of them - see my questions above) should be displayed. Simple ORDER BY will not work, it has to be combined with selecting just some of the rows, for example one row for each ThreadID.
October 1, 2007 at 8:45 am
The DateSent column is enough - it is not practical there would be two emails in the same thread with the exact DateSent value.
As for the front end control performing this functionality, unfortunately that is not the case - at least for the one we use. We must have two separate ADO.Net datatables. One for the parent table, one for the child. The ThreadID value is used to define the link/relationship.
October 1, 2007 at 8:57 am
This should work for the first output table, if DateSent is unique for each ThreadID. If it isn't, it will return several rows for such ThreadID:
SELECT m.EmailID, m.ClientID, m.DateSent, m.Subject, m.ThreadID
FROM #mail m
JOIN (select ThreadID, MAX(DateSent) as maxdate
from #mail
group by ThreadID) as Q ON Q.ThreadID=m.ThreadID AND Q.maxdate = m.DateSent
... and, if it does what you need, then this should give you the second table.
SELECT m.EmailID, m.ClientID, m.DateSent, m.Subject, m.ThreadID
FROM #mail m
LEFT JOIN (select ThreadID, MAX(DateSent) as maxdate
from #mail
group by ThreadID) as Q ON Q.ThreadID=m.ThreadID AND Q.maxdate = m.DateSent
WHERE Q.ThreadID IS NULL
EDIT: Sorry, you didn't specify table name so I used table name #mail, You need to replace that with your actual table name.
October 1, 2007 at 9:04 am
Thank you Vladan - that does seem to work and will get me going.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply