respond to the last m.sender

TABLE `a_messages2`
  `a_messages_id` int(11) NOT NULL auto_increment
  `conversation_id` bigint(20)
  `profile_id` varchar(20)
  `sender` varchar(20)
  `message_id` bigint(20)
  `message_text` varchar(1000)
  `dateAgo` varchar(20)
  `message_read` tinyint(4)
  `this_user` varchar(20)
  PRIMARY KEY  (`a_messages_id`)
  UNIQUE KEY `unique_message_id` (`message_id`)




this is a query of conversations sorted alphabetically by profile_id where the conversation is more than 1 message that is not sent by me
AND m.sender != m.this_user /* is this "not sent by me" ? I don't know your data */


SELECT
        m.profile_id
      , m.sender
      , mc.profile_id_count
      , m.message_text
      , m.conversation_id
      , m.dateAgo
      , m.message_id
      , m.message_read
      , s.comment
FROM a_messages2 m
LEFT JOIN search s
        ON m.profile_id = s.profile_id
JOIN (
        SELECT
                  profile_id
                , COUNT(profile_id) AS profile_id_count
        FROM a_messages2
        GROUP BY profile_id
        )
        mc
        ON m.profile_id = mc.profile_id
WHERE mc.profile_id_count > 1

AND m.sender != m.this_user /* is this "not sent by me" ? I don't know your data */

ORDER BY
        m.profile_id, m.this_user, message_id DESC

Open in new window




m.sender != m.this_user works to show the messages that were not sent by me

but I am looking for the rows with highest message_id in the conversation_id where m.sender != m.this_user
because I want to see the conversation_id that I need to respond to
because if another m.sender sent me the last message in a conversation, then I need to respond to that m.sender

but if the last m.sender was me then I do not need to send another message.
LVL 1
rgb192Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
Not sure I follow all of the question, but I can help with one part of it.  If you want the most recent of anything in a MySQL table, you do it this way:

Table definition includes a column of the type TIMESTAMP, let's call it when_added

Query SELECTs whatever it needs and uses ORDER BY when_added DESC LIMIT 1
0
rgb192Author Commented:
Query SELECTs whatever it needs and uses ORDER BY when_added DESC LIMIT 1

I can order by message_id

because the highest message_id in the conversation is the last message sent

so I do not have to use datetime or sort by datetime
0
Ray PaseurCommented:
Message_id tells you a number.  TIMESTAMP values can be queried to show a trend, arrival rate, frequency distributions, etc.  It's your choice, but I would want to be able to get the answer when the director of marketing comes into the office and says, "How many people signed up last week?"

Also, note that DATETIME and TIMESTAMP are different column data types with different functionality.
http://www.w3schools.com/sql/
http://www.w3schools.com/sql/sql_datatypes_general.asp
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

PortletPaulfreelancerCommented:
`dateAgo` varchar(20) :: What does this field actually do? & Why is it varchar?

I agree with Ray, it would be preferable to base the "latest unanswered message" state on date/time information rather than an identity field. But, either way, I think the technique you need is the same (but the fields used would differ).
---------------

I believe you are asking for "a list of the latest unanswered messages - per sender"

MySQL lacks the analytic function row_number() which is extremely useful in meeting needs such as you want here. To emulate this function requires the use of 2 @variables and an ordered sub-query. This blog neatly describes the technique:
http://www.explodybits.com/2011/11/mysql-row-number/

And: I think the technique could be used this way:
SELECT
        m.profile_id
      , m.sender
      , mc.profile_id_count
      , m.message_text
      , m.conversation_id
      , m.dateAgo
      , m.message_id
      , m.message_read
      , s.comment
FROM (
        SELECT
                @row_num := IF(@prev_value = sender, @row_num + 1, 1) AS RowNumber
                profile_id
              , sender
              , message_text
              , conversation_id
              , dateAgo
              , message_id
              , message_read
              , @prev_value := sender AS partition_by
        FROM a_messages2
        CROSS JOIN (SELECT @row_num := 1 AS x, @prev_value := '' AS y) AS cj

        WHERE sender != this_user /* not sent by me condition moved to here */

        ORDER BY sender, message_id DESC
     ) AS m
LEFT JOIN search AS s
        ON m.profile_id = s.profile_id
INNER JOIN (
                SELECT
                          profile_id
                        , COUNT(profile_id) AS profile_id_count
                FROM a_messages2
                GROUP BY profile_id
                HAVING COUNT(profile_id) > 1 /* this replaces a previous where condition in the outer query */
           ) AS mc
                ON m.profile_id = mc.profile_id

WHERE m.RowNumber = 1 /* this should provide "the latest message - per sender " */

ORDER BY
        m.profile_id
      , m.this_user
      /* , message_id DESC */
/* as there should only be one message per sender order in message_id is probably not needed now */
;

Open in new window

btw: I think you could remove this from the query:
/* is this "not sent by me" ? I don't know your data */

any comments I have included in the code block above are not intended to be permanent
blank line are also intended simply to draw your attention to changes, the blank lines may be removed.

{+edit} It may be more efficient to move that group by sub-query too. This would reduce the number of records being ordered while assessing for RowNumber
SELECT
        m.profile_id
      , m.sender
      , mc.profile_id_count
      , m.message_text
      , m.conversation_id
      , m.dateAgo
      , m.message_id
      , m.message_read
      , s.comment
FROM (
        SELECT
                @row_num := IF(@prev_value = sender, @row_num + 1, 1) AS RowNumber
                profile_id
              , sender
              , message_text
              , conversation_id
              , dateAgo
              , message_id
              , message_read
              , @prev_value := sender AS partition_by
        FROM a_messages2
        CROSS JOIN (SELECT @row_num := 1 AS x, @prev_value := '' AS y) AS cj
        INNER JOIN (
                        SELECT
                                  profile_id
                                , COUNT(profile_id) AS profile_id_count
                        FROM a_messages2
                        GROUP BY profile_id
                        HAVING COUNT(profile_id) > 1 /* this replaces a previous where condition in the outer query */
                   ) AS mc
                        ON a_messages2.profile_id = mc.profile_id

        WHERE a_messages2.sender != a_messages2.this_user /* not sent by me condition moved to here */

        ORDER BY sender, message_id DESC
     ) AS m
LEFT JOIN search AS s
        ON m.profile_id = s.profile_id

WHERE m.RowNumber = 1 /* this should provide "the latest message - per sender " */

ORDER BY
        m.profile_id
      , m.this_user
      /* , message_id DESC */
/* as there should only be one message per sender order in message_id is probably not needed now */
;

Open in new window

Note as you have not provided any sample data or expected results - no code suggestions have been tested.
0
PortletPaulfreelancerCommented:
It's probably worthwhile pointing out that this query is scanning through the messages table twice (for "the latest" - which includes a sort - and for the "group by" (also requires a sort); and that for most of the operations there are no relevant indexes.

In short, this may be a very expensive query, particularly as the number of records grow.

You might need indexes on profile_id and sender - but remember that adding indexes may help with queries but slow down inserts.
0
rgb192Author Commented:
PortletPaul

I believe you are asking for "a list of the latest unanswered messages - per sender"
yes


dateAgo: I will change from varchar to datetime in the next question



first query:
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'profile_id               , sender               , message_text               , c' at line 14




second query:
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'profile_id               , sender               , message_text               , c' at line 14
0
PortletPaulfreelancerCommented:
line 14 is missing a comma - sorry - it should be:
SELECT
        m.profile_id
      , m.sender
      , mc.profile_id_count
      , m.message_text
      , m.conversation_id
      , m.dateAgo
      , m.message_id
      , m.message_read
      , s.comment
FROM (
        SELECT
                @row_num := IF(@prev_value = sender, @row_num + 1, 1) AS RowNumber
              , profile_id /* missing comma repaired */
              , sender
              , message_text
              , conversation_id
              , dateAgo
              , message_id
              , message_read
              , @prev_value := sender AS partition_by
        FROM a_messages2
        CROSS JOIN (SELECT @row_num := 1 AS x, @prev_value := '' AS y) AS cj
        INNER JOIN (
                        SELECT
                                  profile_id
                                , COUNT(profile_id) AS profile_id_count
                        FROM a_messages2
                        GROUP BY profile_id
                        HAVING COUNT(profile_id) > 1 /* this replaces a previous where condition in the outer query */
                   ) AS mc
                        ON a_messages2.profile_id = mc.profile_id

        WHERE a_messages2.sender != a_messages2.this_user /* not sent by me condition moved to here */

        ORDER BY sender, message_id DESC
     ) AS m
LEFT JOIN search AS s
        ON m.profile_id = s.profile_id

WHERE m.RowNumber = 1 /* this should provide "the latest message - per sender " */

ORDER BY
        m.profile_id
      , m.this_user
      /* , message_id DESC */
/* as there should only be one message per sender order in message_id is probably not needed now */
;

Open in new window

once this is running you may want to remove some of the comments included above - BUT, do include comments that explains the logic for reference later.
0
rgb192Author Commented:
Error Code: 1052. Column 'profile_id' in field list is ambiguous

I do not know which line
0
PortletPaulfreelancerCommented:
debugging is largely down to you as I cannot run the query - the objective is to put you on the correct path. I did omit to use aliases and that's not good - this comes about by trying to be quick in response - and we often assume you will take our concepts rather than expecting fully pledged production quality code. However I noticed something else at line 4 which would failed so here is another attempt. Please try this, and if it errors please attempt correction - if stuck then ask for more assistance.
SELECT
        m.profile_id
      , m.sender
      , m.profile_id_count
      , m.message_text
      , m.conversation_id
      , m.dateAgo
      , m.message_id
      , m.message_read
      , s.comment
FROM (
        SELECT
                @row_num := IF(@prev_value = a_messages2.sender, @row_num + 1, 1) AS RowNumber
              , a_messages2.profile_id
              , a_messages2.sender
              , a_messages2.message_text
              , a_messages2.conversation_id
              , a_messages2.dateAgo
              , a_messages2.message_id
              , a_messages2.message_read
              . mc.profile_id_count
              , @prev_value := a_messages2.sender AS partition_by
        FROM a_messages2
        CROSS JOIN (SELECT @row_num := 1 AS x, @prev_value := '' AS y) AS cj
        INNER JOIN (
                        SELECT
                                  profile_id
                                , COUNT(profile_id) AS profile_id_count
                        FROM a_messages2
                        GROUP BY profile_id
                        HAVING COUNT(profile_id) > 1 /* this replaces a previous where condition in the outer query */
                   ) AS mc
                        ON a_messages2.profile_id = mc.profile_id

        WHERE a_messages2.sender != a_messages2.this_user /* not sent by me condition moved to here */

        ORDER BY a_messages2.sender, a_messages2.message_id DESC
     ) AS m
LEFT JOIN search AS s
        ON m.profile_id = s.profile_id

WHERE m.RowNumber = 1 /* this should provide "the latest message - per sender " */

ORDER BY
        m.profile_id
      , m.this_user
      /* , message_id DESC */
/* as there should only be one message per sender order in message_id is probably not needed now */
;

Open in new window

0
rgb192Author Commented:
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.profile_id_count               , @prev_value := a_messages2.sender AS partition' at line 21
0
PortletPaulfreelancerCommented:
if you expect me to debug this you must provide the table definitions - table definitions plus sample data would really be required.

I have provided an answer - which is the technique to use to meet the requirement - as it is not possible for me to test any code here I am not able to be a qa/test engineer too. Please understand this - I am not able to test anything as I don't have access to the database.

additionally - and this may seem weird to you - but I have no idea what variant of the code you are executing either.

at a bare minimum please supply the exact code you last executed
0
PortletPaulfreelancerCommented:
by the way that error message has a dot before the word period, it should be a comma

I didn't suggest use of a dot
0
rgb192Author Commented:
>>if you expect me to debug this you must provide the table definitions



Create TABLE `a_messages2`
  `a_messages_id` int(11) NOT NULL auto_increment
  `conversation_id` bigint(20)
  `profile_id` varchar(20)
  `sender` varchar(20)
  `message_id` bigint(20)
  `message_text` varchar(1000)
  `dateAgo` varchar(20)
  `message_read` tinyint(4)
  `this_user` varchar(20)
  PRIMARY KEY  (`a_messages_id`)
  UNIQUE KEY `unique_message_id` (`message_id`)


CREATE TABLE `search` (
  `search_id` int(11) NOT NULL auto_increment,
  `profile_id` varchar(20) default NULL,
  `wrong` tinyint(4) default NULL,
  `this_user` varchar(20) default NULL,
  `sent` tinyint(4) default NULL,
  `friend` tinyint(4) default NULL,
  `comment` varchar(1000) default NULL,
  PRIMARY KEY  (`search_id`)
) ENGINE=MyISAM AUTO_INCREMENT=4506 DEFAULT CHARSET=utf8;

>>at a bare minimum please supply the exact code you last executed
I do not understand
0
rgb192Author Commented:
I will try to provide test data because I think these are the only columns used to determine the last sender for conversation


TABLE `a_messages2`
  `a_messages_id` int(11) NOT NULL auto_increment
  `conversation_id` bigint(20)
  `profile_id` varchar(20)
  `sender` varchar(20)
  `message_id` bigint(20)
  `this_user` varchar(20)
0
rgb192Author Commented:
by the way that error message has a dot before the word period, it should be a comma

I didn't suggest use of a dot


I do not understand where is the
.
and where is the
,
0
PortletPaulfreelancerCommented:
http:#a39563361

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.profile_id_count              , @prev_value := a_messages2.sender AS partition' at line 21

I made (yet another error) and cannot believe I did it. One of the very reasons I use "comma first" layout is to avoid such silliness; darn.
SELECT
        m.profile_id
      , m.sender
      , m.profile_id_count
      , m.message_text
      , m.conversation_id
      , m.dateAgo
      , m.message_id
      , m.message_read
      , s.comment
FROM (
        SELECT
                @row_num := IF(@prev_value = a_messages2.sender, @row_num + 1, 1) AS RowNumber
              , a_messages2.profile_id
              , a_messages2.sender
              , a_messages2.message_text
              , a_messages2.conversation_id
              , a_messages2.dateAgo
              , a_messages2.message_id
              , a_messages2.message_read
              , mc.profile_id_count /* THE DOT ERREOR WAS HERE - ooops */
              , @prev_value := a_messages2.sender AS partition_by
        FROM a_messages2
        CROSS JOIN (SELECT @row_num := 1 AS x, @prev_value := '' AS y) AS cj
        INNER JOIN (
                        SELECT
                                  profile_id
                                , COUNT(profile_id) AS profile_id_count
                        FROM a_messages2
                        GROUP BY profile_id
                        HAVING COUNT(profile_id) > 1 /* this replaces a previous where condition in the outer query */
                   ) AS mc
                        ON a_messages2.profile_id = mc.profile_id

        WHERE a_messages2.sender != a_messages2.this_user /* not sent by me condition moved to here */

        ORDER BY a_messages2.sender, a_messages2.message_id DESC
     ) AS m
LEFT JOIN search AS s
        ON m.profile_id = s.profile_id

WHERE m.RowNumber = 1 /* this should provide "the latest message - per sender " */

ORDER BY
        m.profile_id

      , m.sender /* this was changed from:   m.this_user   */


      /* , message_id DESC */
/* as there should only be one message per sender order in message_id is probably not needed now */
;

Open in new window

I will see if i can test this against your tables as well - but that dot was on line 21.

To be honest, I'd expect you to be able to see this too. If using SQL, it's useful to know how to solve such errors.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
>>at a bare minimum please supply the exact code you last executed
I do not understand

please note: This may seem impolite, but I don't mean it to be.

When you send back error messages you assume I know what it is you just ran. I do not.
Did you use your own code that you edited? I don't know
Did you use my code, but is my line 1 your line 1? I don't know
Also consider there are many code blocks here - which one are you using? I don't know

Hence, when sending a series of error messages: I do not know what code you just ran.
That was the point I was trying to make.

This has happened to me often in the past - and until the code you are executing in known sometimes we might just go around in circles; that is what I was trying to avoid.

I'll attempt to test my latest code against your tables now. Hopefully all kinks will then be ironed out. I hope you realize I'm a volunteer.
0
PortletPaulfreelancerCommented:
Schema Creation Failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`a_messages_id` int(11) NOT NULL auto_increment
  `conversation_id` bigint(20)
' at line 2:
0
PortletPaulfreelancerCommented:
ok, got your DDL to work,
and the query at http:#a39567472 has no syntax errors I can detect

see: http://sqlfiddle.com/#!9/9ee48/3
0
rgb192Author Commented:
Thank you for explanation and query

I have a related question because I want only a list of users that are waiting for my response
http://www.experts-exchange.com/Database/MySQL/Q_28265673.html
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.