473,320 Members | 2,109 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Selecting 1 Record per Foreign Key

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
Jul 20 '05 #1
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]
Jul 20 '05 #2
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.
Jul 20 '05 #3
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.
Jul 20 '05 #4
"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

Jul 20 '05 #5
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]
Jul 20 '05 #6
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.
Jul 20 '05 #7
"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

Jul 20 '05 #8
Thank you all for your help!
-Ben
Jul 20 '05 #9
"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
Jul 20 '05 #10
Thank you all for your help!
-Ben
Jul 20 '05 #11
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.


Jul 20 '05 #12
"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
Jul 20 '05 #13
"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
Jul 20 '05 #14
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.

Jul 20 '05 #15
"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
Jul 20 '05 #16
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.


Jul 20 '05 #17
"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
Jul 20 '05 #18
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.

Jul 20 '05 #19
"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
Jul 20 '05 #20

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
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....
1
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...
1
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...
8
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...
5
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...
7
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...
6
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...
6
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...
5
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...
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
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...
0
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...
0
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...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
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....
0
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
0
isladogs
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.