Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

indicate row or profile_id which needs to be responded to

Posted on 2013-10-29
3
313 Views
Last Modified: 2013-11-08
CREATE TABLE `a_messages2` (
       `a_messages_id` INT (11) auto_increment  primary key
     , `profile_id` VARCHAR(20)
     , `sender` VARCHAR(20)
     , `message_id` BIGINT (20)
     , `this_user` VARCHAR(20)
     );



INSERT INTO a_messages2
          (`profile_id`, `sender`, `message_id`, `this_user`)
    VALUES
('abl', 'staff3', 3662615602, 'staff3'),
('abl', 'abl', 3660687080, 'staff3'),
('abl', 'staff3', 3658803944, 'staff3'),
('abl', 'staff3', 3649952930, 'staff3'),
('abl', 'staff3', 3644250249, 'staff3'),
('abl', 'staff3', 3614235515, 'staff3'),
('abl', 'abl', 3614140442, 'staff3'),
('abl', 'staff3', 3613984751, 'staff3'),
('abl', 'staff3', 3613936332, 'staff3'),
('abl', 'abl', 3613469574, 'staff3'),
('abl', 'staff3', 3612970481, 'staff3'),
('abl', 'abl', 3609276901, 'staff3'),
('abl', 'abl', 3609276896, 'staff3'),
('abl', 'staff3', 3866086756, 'staff3'),
('abl', 'staff3', 3866079064, 'staff3'),
('abl', 'abl', 3856964132, 'staff3'),
('abl', 'staff3', 3844779912, 'staff3'),
('abl', 'staff3', 3916948647, 'staff3'),
('abl', 'staff3', 3880367425, 'staff3'),
('abl', 'abl', 3977088222, 'staff3'),
('abl', 'staff3', 3974513295, 'staff3'),
('abl', 'staff3', 3973285282, 'staff3'),
('abl', 'abl', 3960907806, 'staff3'),
('abl', 'staff3', 4003664468, 'staff3'),
('abl', 'abl', 4002246444, 'staff3'),
('abl', 'staff3', 3996089944, 'staff3'),
('abl', 'staff3', 4024726432, 'staff3'),
('abl', 'abl', 4021516823, 'staff3'),
('abl', 'staff3', 4018957318, 'staff3'),
('abl', 'abl', 4050609080, 'staff3'),
('abl', 'staff3', 4052368867, 'staff3'),
('abl', 'abl', 4052424820, 'staff3'),
('abl', 'staff3', 4053183569, 'staff3'),
  
('swa','swa','4153199171','staff3'),
('swa','staff3','4150323049','staff3'),
('swa','swa','4146362776','staff3'),
('swa','staff3','4142884979','staff3'),
('swa','staff3','4142884976','staff3')

Open in new window





need to find out if a customer is waiting for a staff member to respond


Real world explanation:
Respond to the customers waiting for a response.

there should be no results because
select profile_id, sender,message_id,this_user from a_messages2 where profile_id='abl' order by message_id desc limit 1

abl  staff3  4053183569  staff3


staff3 was the last to respond

we do not need to send another message to 'abl' because staff3 already sent the last message


but there should be results for swa, because we need to respond to swa
0
Comment
Question by:rgb192
  • 2
3 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39610832
THis result:
| SENDER | MESSAGE_ID |
|--------|------------|
|    swa | 4153199171 |

Open in new window

produced by this query:
SELECT
        mx.sender
      , mx.message_id
FROM (
      SELECT
        profile_id
      , sender
      , max(message_id) AS message_id
      FROM a_messages2
      WHERE profile_id=sender
      GROUP BY
        profile_id
      , sender
     ) AS mx
LEFT JOIN a_messages2 AS m ON mx.profile_id =  m.profile_id
                          AND mx.sender     <> m.sender
                          AND mx.message_id <  m.message_id
WHERE m.a_messages_id IS NULL
;

/* http://sqlfiddle.com/#!9/2093c/7 */

Open in new window

0
 

Author Closing Comment

by:rgb192
ID: 39618927
Works great
Thank you


I have a related question:
discovering if the customer sent the first message
http://www.experts-exchange.com/Database/MySQL/Q_28283780.html
0
 

Author Comment

by:rgb192
ID: 39633373
Thanks for your help

I have a related question:
I want to see all the rows of data which I need to respond to (the messages I sent and received)
http://www.experts-exchange.com/Database/MySQL/Q_28289041.html
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How many transactions can mysql handle? 3 42
MySqlDump not dumping triggers 1 43
PHP & MySql - Turn Select Query Results into a Glyphicon 3 39
Insert values are dynamic 11 51
Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question