SEO

hello

i have a message table like this:

created datetime
sender int
recipient int
message text

to get all my messages i would do:

select * from message where sender = $MYID or recipient = $MYID

but how do i make a query that returns the rows of only the NEWEST
messages between myself and my contacts (regardless of who sent the
newest message)?

i tried using "group-wise maximum" mentioned in the mysql manual, but
can't figure it out. hope someone can help. thanks!

just to clarify, i want the

1. the newest message between myself and person A
2. the newest message between myself and person B
3. the newest message between myself and person C
4. the newest message between myself and person D

.... and so on

> hello
>
> i have a message table like this:
>
> created datetime
> sender int
> recipient int
> message text
>
> to get all my messages i would do:
>
> select * from message where sender = $MYID or recipient = $MYID
>
> but how do i make a query that returns the rows of only the NEWEST
> messages between myself and my contacts (regardless of who sent the
> newest message)?

Define "newest".

> i tried using "group-wise maximum" mentioned in the mysql manual, but
> can't figure it out. hope someone can help. thanks!

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

> hi, i posted this clarification just after the original posting.
>
> what i want is:
>
> 1. the newest message between myself and person A
> 2. the newest message between myself and person B
> 3. the newest message between myself and person C
> 4. the newest message between myself and person D
>
> ... and so on

Still, define "newest":

- newest according to date?
- newest not yet read message?
- something else?

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

On 28/04/2008, Martijn Tonies <m.tonies@upscene.com> wrote:

> > 1. the newest message between myself and person A
> > 2. the newest message between myself and person B
> > 3. the newest message between myself and person C
> > 4. the newest message between myself and person D

> Still, define "newest":
>
> - newest according to date?
> - newest not yet read message?
> - something else?

newest according to date of creation.

> On 28/04/2008, Martijn Tonies <m.tonies@upscene.com> wrote:
>
> > > 1. the newest message between myself and person A
> > > 2. the newest message between myself and person B
> > > 3. the newest message between myself and person C
> > > 4. the newest message between myself and person D
>
> > Still, define "newest":
> >
> > - newest according to date?
> > - newest not yet read message?
> > - something else?
>
> newest according to date of creation.

There are probably other solutions, but this seems to work:

select *
from ( select * from msgs order by msgs.created desc ) t
where sender = 1 or recipient = 1
group by sender, recipient

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

On 28/04/2008, Martijn Tonies <m.tonies@upscene.com> wrote:

> select *
> from ( select * from msgs order by msgs.created desc ) t
> where sender = 1 or recipient = 1
> group by sender, recipient

not quite right. first comes all of MY newest messages, then comes all
of THEIR newest messages. for example:

S R
1 2
1 3
1 5
1 7
2 1
5 1

the final result should include EITHER "1 2" OR "2 1", depending on
which of the two is newest. same for "1 5" and "5 1".

> > select *
> > from ( select * from msgs order by msgs.created desc ) t
> > where sender = 1 or recipient = 1
> > group by sender, recipient
>
> not quite right. first comes all of MY newest messages, then comes all
> of THEIR newest messages. for example:
>
> S R
> 1 2
> 1 3
> 1 5
> 1 7
> 2 1
> 5 1
>
> the final result should include EITHER "1 2" OR "2 1", depending on
> which of the two is newest. same for "1 5" and "5 1".

So, a newest message from "me to A" would exclude the newest message
from "A to me" (depending, of course, on the creation date)?

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

Exactly.

2008/4/28, Martijn Tonies <m.tonies@upscene.com>:
>
> > > select *
> > > from ( select * from msgs order by msgs.created desc ) t
> > > where sender = 1 or recipient = 1
> > > group by sender, recipient
> >
> > not quite right. first comes all of MY newest messages, then comes all
> > of THEIR newest messages. for example:
> >
> > S R
> > 1 2
> > 1 3
> > 1 5
> > 1 7
> > 2 1
> > 5 1
> >
> > the final result should include EITHER "1 2" OR "2 1", depending on
> > which of the two is newest. same for "1 5" and "5 1".
>
> So, a newest message from "me to A" would exclude the newest message
> from "A to me" (depending, of course, on the creation date)?
>
> Martijn Tonies
> Database Workbench - development tool for MySQL, and more!
> Upscene Productions
> http://www.upscene.com
> My thoughts:
> http://blog.upscene.com/martijn/
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>