How to Group SQL result

Question

I have following data in database

MAILFROM, MAILTO , TIMESTAMP, MESSAGE
A B   2013-07-01 12:11:12, Hi
B A   2013-07-01 12:12:12, Hi back
A B   2013-07-01 12:13:12, How are you
A C   2013-07-01 12:14:12, Hi there
D A   2013-07-01 12:16:12, Hi
C D   2013-07-01 12:17:12, Hi

How do I group this with select so I get

A C 'comment occurs 3 times

SELECT MAILFROM, MAILTO FROM messages WHERE 'A' IN(FROM,TO) GROUP BY FROM

gives

A C as well as C A but I want combination combined together.

that it only shows A C 3 times

The example is a mailbox.

This contains:

MAILFROM, MAILTO , TIMESTAMP, MESSAGE
A B   2013-07-01 12:11:12, Hi
B A   2013-07-01 12:12:12, Hi back
A B   2013-07-01 12:13:12, How are you
A C   2013-07-01 12:14:12, Hi there
D A   2013-07-01 12:16:12, Hi
C D   2013-07-01 12:17:12, Hi

SQL listing should list this (unique conversations)

B   2013-07-01 12:13:12, "Hi"  ' Remark Timestap of the latest message
C   2013-07-01 12:14:12, "Hi there"
D   2013-07-01 12:16:12, "Hi"
C D   2013-07-01 12:17:12, "Hi" ' THIS SHOULD NOT BE SHOWN

This means this sql will list the messages he have as sender and as receiver (from,to). It should only list between this person and the one sent to no matter who is MAILFROM or MAILTO. timestamp is the date of the latest message between them... Remark he never send to D, a is listed anyhow, to C he sent but didn not get back anything... between B is 3 messages. so output should be only these 3 rows..

Answer 1

Many databases support the least() and greatest() functions. You can do what you want as:

select least("from", "to") as party1,
       greatest("from", "to") as party2,
       count(*) as NumMessages,
       max(timestamp) as maxtimestamp
from messages
group by least("from", "to"), greatest("from", "to") ;

The following uses the case isntead (standard SQL) and should work in most databases:

select (case when "from" < "to" then "from" else "to" end) as party1,
       (case when "from" < "to" then "to" else "from" end) as party2,
       count(*) as NumMessages,
       max(timestamp) as maxtimestamp
from messages
group by (case when "from" < "to" then "from" else "to" end),
         (case when "from" < "to" then "to" else "from" end)

EDIT:

If you want this as unique messages for a given person:

select (case when "from" = const.ThePerson then "to" else "from" end) as Other,
       count(*) as NumMessages,
       max(timestamp) as maxtimestamp
from messages m cross join
     (select 'A' as ThePerson) const
where const.ThePerson in ("from", "to")
group by "from", "to";

To get the last message, you need to join back in the original data:

select Other, NumMessages, MaxTimeStamp, m.message
from (select (case when "from" = const.ThePerson then "to" else "from" end) as Other,
             count(*) as NumMessages,
             max(timestamp) as maxtimestamp,
             max(ThePerson) as ThePerson,
      from messages m cross join
           (select 'A' as ThePerson) const
      where const.ThePerson in ("from", "to")
      group by "from", "to"
     ) t join
     messages m
     on m."from" in (t.Other, t.ThePerson) and
        m."to" in (t.Other, t.ThePerson) and
        m.TimeStamp = t.maxtimestamp