Hello!
I am trying to query a MySQL table and retrieve either the earliest or the
latest message in each thread. The messages are in a table with MsgID being
the primary key and fThreadID identifying to which thread the message
belongs.
Examples below.
Any ideas on the SQL I need to use here?
Thank you!
Ben :-)
Ben Gribaudo - Baltimore, MD - www.bengribaudo.com
"For God so loved the world, that he gave his only begotten Son, that
whosoever believeth in him should not perish, but have everlasting life."
John 3:16
Example Table
=============
MsgID (primary key) fThreadID ReceivedTimestamp (DateTime)
1 99 2004-01-02 11:10:15
2 99 2004-02-01 01:11:59
3 99 2004-05-17 18:15:01
4 102 2004-07-01 11:11:29
4 103 2004-07-01 11:11:11
Example Desired Result Set #1
(earliest msg from each thread)
===============================
MsgID, fThreadID, ReceivedTimestamp
1 99 2004-01-02 11:10:15
4 103 2004-07-01 11:11:11
Example Desired Result Set #2
(most recent msg from each thread)
==================================
MsgID fThreadID ReceivedTimestamp
3 99 2004-05-17 18:15:01
4 102 2004-07-01 11:11:29 19 3658
Hi Bill,
Thank you for your reply! Maybe not likely in the real world, but as far as your database knows, there's nothing preventing it from occurring.
And our job, as programmers, is to design our scripts/programs to handle
such things.
Is this a typo? Do you mean to list another value such as 5 as the MsgID in the last row?
Yes.
You had three distinct fThreadID values in the example dataset. I would expect three rows in the result set of this query.
I meant for fThreadID 103 to be 102, making two unique fThreadIDs. Today
must be my day for typos. :o)
I think this is a case that is necessary to solve in two queries: select t1.fThreadID, max(t1.ReceivedTimestamp) as from MyTable t1 group by t1.fThreadID;
Then loop through that result set and get the MsgID values matching each one: select t2.MsgID from MyTable t2 where t2.fThreadID = ? and t2.ReceivedTimestamp = ? limit 1;
Thank you. I hadn't thought of doing the second query that way.
I may have to drop the whole idea of doing this. The goal was to be able to
sort a set of threads in alphabetical order by the from name of either the
earliest or last message in that thread for a forum script. I'm not sure if
the feature will give enough advantage to justify the extra looped querying
(modifying the object to support it, query performance, etc.).
Thank you, again, for your help!
-Ben
"Bill Karwin" <bi**@karwin.com> wrote in message
news:cd********@enews1.newsguy.com...
[snip] This is one of those tricky cases in SQL, and this sort of problem comes up periodically. You want to aggregate using MIN() or MAX(), use GROUP BY to get a row of output per fThreadID, but you also want an attribute of the row that contained that MAX value. So one could imagine this hypothetical SQL:
select (select t2.MsgID from MyTable t2 where t2.fThreadID = t1.fThreadID and t2.ReceivedTimestamp = max(t1.ReceivedTimestamp)) as MsgID, t1.fThreadID, max(t1.ReceivedTimestamp) from MyTable t1 group by t1.fThreadID;
But this makes no sense. The result of the subquery is not guaranteed to be a singleton value, because even though MsgID is a primary key and must be unique, it is possible that two different MsgID's have identical timestamps. Maybe not likely in the real world, but as far as your database knows, there's nothing preventing it from occurring.
I think this is a case that is necessary to solve in two queries: select t1.fThreadID, max(t1.ReceivedTimestamp) as from MyTable t1 group by t1.fThreadID;
Then loop through that result set and get the MsgID values matching each one: select t2.MsgID from MyTable t2 where t2.fThreadID = ? and t2.ReceivedTimestamp = ? limit 1;
Example Table ============= MsgID (primary key) fThreadID ReceivedTimestamp (DateTime) 1 99 2004-01-02 11:10:15 2 99 2004-02-01 01:11:59 3 99 2004-05-17 18:15:01 4 102 2004-07-01 11:11:29 4 103 2004-07-01 11:11:11
Is this a typo? Do you mean to list another value such as 5 as the MsgID in the last row?
Example Desired Result Set #1 (earliest msg from each thread) =============================== MsgID, fThreadID, ReceivedTimestamp 1 99 2004-01-02 11:10:15 4 103 2004-07-01 11:11:11
You had three distinct fThreadID values in the example dataset. I would expect three rows in the result set of this query.
[snip]
Ben Gribaudo wrote: Hello!
I am trying to query a MySQL table and retrieve either the earliest or the latest message in each thread. The messages are in a table with MsgID being the primary key and fThreadID identifying to which thread the message belongs.
Examples below.
Any ideas on the SQL I need to use here?
This is one of those tricky cases in SQL, and this sort of problem comes
up periodically. You want to aggregate using MIN() or MAX(), use GROUP
BY to get a row of output per fThreadID, but you also want an attribute
of the row that contained that MAX value. So one could imagine this
hypothetical SQL:
select
(select t2.MsgID
from MyTable t2
where t2.fThreadID = t1.fThreadID
and t2.ReceivedTimestamp = max(t1.ReceivedTimestamp)) as MsgID,
t1.fThreadID, max(t1.ReceivedTimestamp)
from MyTable t1
group by t1.fThreadID;
But this makes no sense. The result of the subquery is not guaranteed
to be a singleton value, because even though MsgID is a primary key and
must be unique, it is possible that two different MsgID's have identical
timestamps. Maybe not likely in the real world, but as far as your
database knows, there's nothing preventing it from occurring.
I think this is a case that is necessary to solve in two queries:
select t1.fThreadID, max(t1.ReceivedTimestamp) as
from MyTable t1
group by t1.fThreadID;
Then loop through that result set and get the MsgID values matching each
one:
select t2.MsgID
from MyTable t2
where t2.fThreadID = ? and t2.ReceivedTimestamp = ?
limit 1;
Example Table ============= MsgID (primary key) fThreadID ReceivedTimestamp (DateTime) 1 99 2004-01-02 11:10:15 2 99 2004-02-01 01:11:59 3 99 2004-05-17 18:15:01 4 102 2004-07-01 11:11:29 4 103 2004-07-01 11:11:11
Is this a typo? Do you mean to list another value such as 5 as the
MsgID in the last row?
Example Desired Result Set #1 (earliest msg from each thread) =============================== MsgID, fThreadID, ReceivedTimestamp 1 99 2004-01-02 11:10:15 4 103 2004-07-01 11:11:11
You had three distinct fThreadID values in the example dataset. I would
expect three rows in the result set of this query.
Regards,
Bill K.
Ben Gribaudo wrote: I may have to drop the whole idea of doing this. The goal was to be able to sort a set of threads in alphabetical order by the from name of either the earliest or last message in that thread for a forum script.
You might want to check out a book called "SQL for Smarties" by Joe
Celko. He has a few chapters on operating on recursive or heirarchical
data structures in SQL. Interesting stuff!
Regards,
Bill K.
"Bill Karwin" <bi**@karwin.com> wrote in message
news:cd********@enews2.newsguy.com... Ben Gribaudo wrote: I may have to drop the whole idea of doing this. The goal was to be able
to sort a set of threads in alphabetical order by the from name of either
the earliest or last message in that thread for a forum script.
You might want to check out a book called "SQL for Smarties" by Joe Celko. He has a few chapters on operating on recursive or heirarchical data structures in SQL. Interesting stuff!
He's also got a brand new book on that same topic: http://www.amazon.com/exec/obidos/tg...books&n=507846
Hi Bill,
Thank you for your reply! Maybe not likely in the real world, but as far as your database knows, there's nothing preventing it from occurring.
And our job, as programmers, is to design our scripts/programs to handle
such things.
Is this a typo? Do you mean to list another value such as 5 as the MsgID in the last row?
Yes.
You had three distinct fThreadID values in the example dataset. I would expect three rows in the result set of this query.
I meant for fThreadID 103 to be 102, making two unique fThreadIDs. Today
must be my day for typos. :o)
I think this is a case that is necessary to solve in two queries: select t1.fThreadID, max(t1.ReceivedTimestamp) as from MyTable t1 group by t1.fThreadID;
Then loop through that result set and get the MsgID values matching each one: select t2.MsgID from MyTable t2 where t2.fThreadID = ? and t2.ReceivedTimestamp = ? limit 1;
Thank you. I hadn't thought of doing the second query that way.
I may have to drop the whole idea of doing this. The goal was to be able to
sort a set of threads in alphabetical order by the from name of either the
earliest or last message in that thread for a forum script. I'm not sure if
the feature will give enough advantage to justify the extra looped querying
(modifying the object to support it, query performance, etc.).
Thank you, again, for your help!
-Ben
"Bill Karwin" <bi**@karwin.com> wrote in message
news:cd********@enews1.newsguy.com...
[snip] This is one of those tricky cases in SQL, and this sort of problem comes up periodically. You want to aggregate using MIN() or MAX(), use GROUP BY to get a row of output per fThreadID, but you also want an attribute of the row that contained that MAX value. So one could imagine this hypothetical SQL:
select (select t2.MsgID from MyTable t2 where t2.fThreadID = t1.fThreadID and t2.ReceivedTimestamp = max(t1.ReceivedTimestamp)) as MsgID, t1.fThreadID, max(t1.ReceivedTimestamp) from MyTable t1 group by t1.fThreadID;
But this makes no sense. The result of the subquery is not guaranteed to be a singleton value, because even though MsgID is a primary key and must be unique, it is possible that two different MsgID's have identical timestamps. Maybe not likely in the real world, but as far as your database knows, there's nothing preventing it from occurring.
I think this is a case that is necessary to solve in two queries: select t1.fThreadID, max(t1.ReceivedTimestamp) as from MyTable t1 group by t1.fThreadID;
Then loop through that result set and get the MsgID values matching each one: select t2.MsgID from MyTable t2 where t2.fThreadID = ? and t2.ReceivedTimestamp = ? limit 1;
Example Table ============= MsgID (primary key) fThreadID ReceivedTimestamp (DateTime) 1 99 2004-01-02 11:10:15 2 99 2004-02-01 01:11:59 3 99 2004-05-17 18:15:01 4 102 2004-07-01 11:11:29 4 103 2004-07-01 11:11:11
Is this a typo? Do you mean to list another value such as 5 as the MsgID in the last row?
Example Desired Result Set #1 (earliest msg from each thread) =============================== MsgID, fThreadID, ReceivedTimestamp 1 99 2004-01-02 11:10:15 4 103 2004-07-01 11:11:11
You had three distinct fThreadID values in the example dataset. I would expect three rows in the result set of this query.
[snip]
Ben Gribaudo wrote: I may have to drop the whole idea of doing this. The goal was to be able to sort a set of threads in alphabetical order by the from name of either the earliest or last message in that thread for a forum script.
You might want to check out a book called "SQL for Smarties" by Joe
Celko. He has a few chapters on operating on recursive or heirarchical
data structures in SQL. Interesting stuff!
Regards,
Bill K.
"Bill Karwin" <bi**@karwin.com> wrote in message
news:cd********@enews2.newsguy.com... Ben Gribaudo wrote: I may have to drop the whole idea of doing this. The goal was to be able
to sort a set of threads in alphabetical order by the from name of either
the earliest or last message in that thread for a forum script.
You might want to check out a book called "SQL for Smarties" by Joe Celko. He has a few chapters on operating on recursive or heirarchical data structures in SQL. Interesting stuff!
He's also got a brand new book on that same topic: http://www.amazon.com/exec/obidos/tg...books&n=507846
Thank you all for your help!
-Ben
"Ben Gribaudo" wrote: Thank you all for your help! -Ben
Ben, you said "thread", so I presume you are talking about a
discussion group or bulletin board. In that case, you want to
"denormalize" which means that you would want to keep the id of the
latest and earliest posts written to the topics table. This way, you
avoid doing the costly on-the-fly query that has been talked about
here.
So every time, there is a new post, the topics table is updated. If a
post is deleted, also the topics table is updated. If this approach
is not taken, it would mean serious performance issues, as more lead
is introduced.
-- http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/mySQL-Select...ict131294.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=444060
Thank you all for your help!
-Ben
Hi Steve,
You are correct--this is for discussion group, or, to be exact, a Web
interface to some e-mail lists. Thank you for that idea.
Do you know of any articles which deal with denormalizing for this
application?
Thank you!
-Ben
"steve" wrote... Ben, you said "thread", so I presume you are talking about a discussion group or bulletin board. In that case, you want to "denormalize" which means that you would want to keep the id of the latest and earliest posts written to the topics table. This way, you avoid doing the costly on-the-fly query that has been talked about here.
"Ben Gribaudo" wrote: Hi Steve,
You are correct--this is for discussion group, or, to be exact, a
Web interface to some e-mail lists. Thank you for that idea.
Do you know of any articles which deal with denormalizing for this application?
Thank you! -Ben
"steve" wrote... Ben, you said "thread", so I presume you are talking about a discussion group or bulletin board. In that case, you want to "denormalize" which means that you would want to keep the id of the latest and earliest posts written to the topics table. This way, you avoid doing the costly on-the-fly query that has been talked about here.</font>
Ben, don’t know articles. But you can download phpbb, and see their
data structures (topics table, posts table), and also look at the
script and see how it is done. If you are setting up a discussion
group, I suggest using either phpbb or maybe invision (for free code),
and start building from there. Bulletin board is a complex piece of
code (you’ll see once you get into it), and I would not attempt to
build what I can get for free.
-- http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/mySQL-Select...ict131294.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=445076
"Ben Gribaudo" wrote: Thank you all for your help! -Ben
Ben, you said "thread", so I presume you are talking about a
discussion group or bulletin board. In that case, you want to
"denormalize" which means that you would want to keep the id of the
latest and earliest posts written to the topics table. This way, you
avoid doing the costly on-the-fly query that has been talked about
here.
So every time, there is a new post, the topics table is updated. If a
post is deleted, also the topics table is updated. If this approach
is not taken, it would mean serious performance issues, as more lead
is introduced.
-- http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/mySQL-Select...ict131294.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=444060
Steve,
Thanks for that idea. Bulletin boards *are* complex! I've already spend
about two hundred hours on this and it will probably require another one to
two hundred hours. The specs for this system differ a bit from a traditional
discussion forum, so this is being programmed from scratch.
Thanks again!
Ben
"steve" wrote... Ben, don't know articles. But you can download phpbb, and see their data structures (topics table, posts table), and also look at the script and see how it is done. If you are setting up a discussion group, I suggest using either phpbb or maybe invision (for free code), and start building from there. Bulletin board is a complex piece of code (you'll see once you get into it), and I would not attempt to build what I can get for free.
"Ben Gribaudo" wrote: Steve,
Thanks for that idea. Bulletin boards *are* complex! I’ve already spend about two hundred hours on this and it will probably require
another one to two hundred hours. The specs for this system differ a bit from a traditional discussion forum, so this is being programmed from scratch.
Thanks again!
Ben
"steve" wrote... Ben, don’t know articles. But you can download phpbb, and see their data structures (topics table, posts table), and also look at the script and see how it is done. If you are setting up a discussion group, I suggest using either phpbb or maybe invision (for free code), and start building from there. Bulletin board is a complex piece of code (you’ll see once you get into it), and I would not attempt to build what I can get for free.</font>
Ben, even if programming from scratch, there is a lot you can take
from something like phpbb, e.g. validation routines, etc. etc. It is
well worth a look, and also to look at data structures.
There are basically two kinds of forums, threaded and non-threaded.
If you are interested in threaded, then take a look at Phorum.
-- http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/mySQL-Select...ict131294.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=445236
Hi Steve,
You are correct--this is for discussion group, or, to be exact, a Web
interface to some e-mail lists. Thank you for that idea.
Do you know of any articles which deal with denormalizing for this
application?
Thank you!
-Ben
"steve" wrote... Ben, you said "thread", so I presume you are talking about a discussion group or bulletin board. In that case, you want to "denormalize" which means that you would want to keep the id of the latest and earliest posts written to the topics table. This way, you avoid doing the costly on-the-fly query that has been talked about here.
"Ben Gribaudo" wrote: Hi Steve,
You are correct--this is for discussion group, or, to be exact, a
Web interface to some e-mail lists. Thank you for that idea.
Do you know of any articles which deal with denormalizing for this application?
Thank you! -Ben
"steve" wrote... Ben, you said "thread", so I presume you are talking about a discussion group or bulletin board. In that case, you want to "denormalize" which means that you would want to keep the id of the latest and earliest posts written to the topics table. This way, you avoid doing the costly on-the-fly query that has been talked about here.</font>
Ben, don’t know articles. But you can download phpbb, and see their
data structures (topics table, posts table), and also look at the
script and see how it is done. If you are setting up a discussion
group, I suggest using either phpbb or maybe invision (for free code),
and start building from there. Bulletin board is a complex piece of
code (you’ll see once you get into it), and I would not attempt to
build what I can get for free.
-- http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/mySQL-Select...ict131294.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=445076
Steve,
Thanks for that idea. Bulletin boards *are* complex! I've already spend
about two hundred hours on this and it will probably require another one to
two hundred hours. The specs for this system differ a bit from a traditional
discussion forum, so this is being programmed from scratch.
Thanks again!
Ben
"steve" wrote... Ben, don't know articles. But you can download phpbb, and see their data structures (topics table, posts table), and also look at the script and see how it is done. If you are setting up a discussion group, I suggest using either phpbb or maybe invision (for free code), and start building from there. Bulletin board is a complex piece of code (you'll see once you get into it), and I would not attempt to build what I can get for free.
"Ben Gribaudo" wrote: Steve,
Thanks for that idea. Bulletin boards *are* complex! I’ve already spend about two hundred hours on this and it will probably require
another one to two hundred hours. The specs for this system differ a bit from a traditional discussion forum, so this is being programmed from scratch.
Thanks again!
Ben
"steve" wrote... Ben, don’t know articles. But you can download phpbb, and see their data structures (topics table, posts table), and also look at the script and see how it is done. If you are setting up a discussion group, I suggest using either phpbb or maybe invision (for free code), and start building from there. Bulletin board is a complex piece of code (you’ll see once you get into it), and I would not attempt to build what I can get for free.</font>
Ben, even if programming from scratch, there is a lot you can take
from something like phpbb, e.g. validation routines, etc. etc. It is
well worth a look, and also to look at data structures.
There are basically two kinds of forums, threaded and non-threaded.
If you are interested in threaded, then take a look at Phorum.
-- http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/mySQL-Select...ict131294.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=445236 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: elvin |
last post by:
Okay - apologize in advance for the length, but I want to make sure
all you knowledgeable and helpful people have all the details you need
to hopefully point my newbie rear in the right direction....
|
by: Tony |
last post by:
I have a form in access that has 5 fields for a certain record: 1)Date
mailed, 2)Date received, 3)Date completed 4)Foreign Content amount and
5)record number. If the foreign content (FC)is >15% of...
|
by: GGerard |
last post by:
Hello
I am working with Access 2000
I have two tables joined on a one to many relationship between two fields:
Table1:FieldID (one) is joined to Table2:FieldMyID (many)
Field Properties...
|
by: Bri |
last post by:
Greetings,
I'm having a very strange problem in an AC97 MDB with ODBC Linked tables
to SQL Server 7. The table has an Identity field and a Timestamp field.
The problem is that when a new record...
|
by: Ross A. Finlayson |
last post by:
Hi,
I'm scratching together an Access database. The development box is
Office 95, the deployment box Office 2003.
So anyways I am griping about forms and global variables. Say for
example...
|
by: Stephen Poley |
last post by:
I have the following situation:
- a table of employees, keyed on employee-id;
- a table of training sessions, keyed on session-id;
- a requirement to log who was present at which session, plus...
|
by: AA Arens |
last post by:
Hi,
I have a database with 2 main forms. Contacts and companies. I share
the base with two others via LAN. On the companies form I have buttons
to navigate throught the records
(>400). We are...
|
by: polocar |
last post by:
Hi,
I'm writing a program in Visual C# 2005 Professional Edition.
This program connects to a SQL Server 2005 database called
"Generations" (in which there is only one table, called...
|
by: jpatchak |
last post by:
Hello,
I'm hoping someone can help me. I have a mainform based on a table (let's call it tblMain) with a Primary Key of . There is a subform on this main form based on another table (let's call...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |