new data to test with existing query

http://www.experts-exchange.com/Database/MySQL/Q_28265673.html#a39569988
when I run the query I get this result
there are numerous rows with the same m.profile_id
so if these results become the testing data maybe the query can be slightly modified



test with this data

20HCB  20HCB  3942050286  staff3
20HCB  20HCB  3558156478  staff3
nyr  nyr  4047282127  staff1
dto  dto  3648832235  staff3
number  number  3743502869  staff2
718cr  718cr  3740367521  staff2
evan  evan  3541580679  staff3
eve  eve  3543207655  staff3
evalene  evalene  3988448827  staff3
evalene  evalene  3963462960  staff3
evalene  evalene  3707375550  staff3
evalene  evalene  3682717052  staff3
Marcus  Marcus  3742373998  staff2
nina  nina  3542317141  staff3
aak  aak  3739873140  staff2
abl  abl  3856964132  staff3
abl  abl  3660687080  staff3
abl  abl  4050609080  staff3
abl  abl  4021516823  staff3
abl  abl  4002246444  staff3
abl  abl  3977088222  staff3
acr  acr  3668881431  staff3
ada  ada  3746335321  staff2
ade  ade  3980600698  staff3
ade  ade  3608017471  staff3
Adv  Adv  3749696084  staff2
aer  aer  3742858517  staff2
agg  agg  3543115588  staff3
agm  agm  3551194200  staff3
agm  agm  3950459251  staff3
ake  ake  3917801577  staff3
ake  ake  3713136329  staff3
ake  ake  3694249180  staff3
Befor  Befor  3922239205  staff3
Befor  Befor  3912553102  staff2
Befor  Befor  3668428692  staff3

Open in new window

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.

PortletPaulfreelancerCommented:
Results obtained fro that sample are as follows, could you identify if this is the expected result please? If it is not, please list the expected result.:
| PROFILE_ID |  SENDER | PROFILE_ID_COUNT | MESSAGE_TEXT | CONVERSATION_ID | DATEAGO | MESSAGE_ID | MESSAGE_READ |
|------------|---------|------------------|--------------|-----------------|---------|------------|--------------|
|      20HCB |   20HCB |                2 |       (null) |          (null) |  (null) | 3942050286 |       (null) |
|        abl |     abl |                6 |       (null) |          (null) |  (null) | 4050609080 |       (null) |
|        ade |     ade |                2 |       (null) |          (null) |  (null) | 3980600698 |       (null) |
|        agm |     agm |                2 |       (null) |          (null) |  (null) | 3950459251 |       (null) |
|        ake |     ake |                3 |       (null) |          (null) |  (null) | 3917801577 |       (null) |
|      Befor |   Befor |                3 |       (null) |          (null) |  (null) | 3922239205 |       (null) |
|    evalene | evalene |                4 |       (null) |          (null) |  (null) | 3988448827 |       (null) |

Open in new window

Full details below (no change to query)
    CREATE TABLE `a_messages2` (
           `a_messages_id` INT (11) auto_increment  primary key
         , `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)
         );
    
    CREATE TABLE `search` (
           `search_id` INT (11) auto_increment  primary key
         , `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
         );
    
    
    /*	
    INSERT INTO a_messages2
    	(`profile_id`, `sender`, `message_id`, `this_user`)
    VALUES
    	('amy', 'amy', 99, 'staff1'),
    	('amy', 'amy', 2, 'staff1'),
    	('amy', 'staff1', 1, 'staff1'),
    	('ann', 'ann', 94, 'staff1'),
    	('ann', 'ann', 7, 'staff1'),
    	('ann', 'staff1', 6, 'staff1'),
    	('bob', 'staff1', 14, 'staff1')
    
    
    CREATE TABLE a_messages2
    	(`profile_id` varchar(7), `sender` varchar(7), `message_id` int, `this_user` varchar(6))
    */
    	
    INSERT INTO a_messages2
    	(`profile_id`, `sender`, `message_id`, `this_user`)
    VALUES
    	('20HCB', '20HCB', 3942050286, 'staff3'),
    	('20HCB', '20HCB', 3558156478, 'staff3'),
    	('nyr', 'nyr', 4047282127, 'staff1'),
    	('dto', 'dto', 3648832235, 'staff3'),
    	('number', 'number', 3743502869, 'staff2'),
    	('718cr', '718cr', 3740367521, 'staff2'),
    	('evan', 'evan', 3541580679, 'staff3'),
    	('eve', 'eve', 3543207655, 'staff3'),
    	('evalene', 'evalene', 3988448827, 'staff3'),
    	('evalene', 'evalene', 3963462960, 'staff3'),
    	('evalene', 'evalene', 3707375550, 'staff3'),
    	('evalene', 'evalene', 3682717052, 'staff3'),
    	('Marcus', 'Marcus', 3742373998, 'staff2'),
    	('nina', 'nina', 3542317141, 'staff3'),
    	('aak', 'aak', 3739873140, 'staff2'),
    	('abl', 'abl', 3856964132, 'staff3'),
    	('abl', 'abl', 3660687080, 'staff3'),
    	('abl', 'abl', 4050609080, 'staff3'),
    	('abl', 'abl', 4021516823, 'staff3'),
    	('abl', 'abl', 4002246444, 'staff3'),
    	('abl', 'abl', 3977088222, 'staff3'),
    	('acr', 'acr', 3668881431, 'staff3'),
    	('ada', 'ada', 3746335321, 'staff2'),
    	('ade', 'ade', 3980600698, 'staff3'),
    	('ade', 'ade', 3608017471, 'staff3'),
    	('Adv', 'Adv', 3749696084, 'staff2'),
    	('aer', 'aer', 3742858517, 'staff2'),
    	('agg', 'agg', 3543115588, 'staff3'),
    	('agm', 'agm', 3551194200, 'staff3'),
    	('agm', 'agm', 3950459251, 'staff3'),
    	('ake', 'ake', 3917801577, 'staff3'),
    	('ake', 'ake', 3713136329, 'staff3'),
    	('ake', 'ake', 3694249180, 'staff3'),
    	('Befor', 'Befor', 3922239205, 'staff3'),
    	('Befor', 'Befor', 3912553102, 'staff2'),
    	('Befor', 'Befor', 3668428692, 'staff3')
    ;

**Query 1**:

    SELECT
            m.profile_id
          , m.sender
          , m.profile_id_count
          , m.message_text
          , m.conversation_id
          , m.dateAgo
          , m.message_id
          , m.message_read
    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
                  , a_messages2.this_user
                  , 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
    
    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 */
    

**[Results][2]**:
    
    | PROFILE_ID |  SENDER | PROFILE_ID_COUNT | MESSAGE_TEXT | CONVERSATION_ID | DATEAGO | MESSAGE_ID | MESSAGE_READ |
    |------------|---------|------------------|--------------|-----------------|---------|------------|--------------|
    |      20HCB |   20HCB |                2 |       (null) |          (null) |  (null) | 3942050286 |       (null) |
    |        abl |     abl |                6 |       (null) |          (null) |  (null) | 4050609080 |       (null) |
    |        ade |     ade |                2 |       (null) |          (null) |  (null) | 3980600698 |       (null) |
    |        agm |     agm |                2 |       (null) |          (null) |  (null) | 3950459251 |       (null) |
    |        ake |     ake |                3 |       (null) |          (null) |  (null) | 3917801577 |       (null) |
    |      Befor |   Befor |                3 |       (null) |          (null) |  (null) | 3922239205 |       (null) |
    |    evalene | evalene |                4 |       (null) |          (null) |  (null) | 3988448827 |       (null) |



  [1]: http://sqlfiddle.com/#!9/05941/1

  [2]: http://sqlfiddle.com/#!9/05941/1/0

Open in new window

0
rgb192Author Commented:
can you try with these rows.

conversation_id, profile_id, sender, message_id, this_user=staff3
695516470  abl  staff3  3662615602
695516470  abl  abl  3660687080
695516470  abl  staff3  3658803944
695516470  abl  staff3  3649952930
695516470  abl  staff3  3644250249
695516470  abl  staff3  3614235515
695516470  abl  abl  3614140442
695516470  abl  staff3  3613984751
695516470  abl  staff3  3613936332
695516470  abl  abl  3613469574
695516470  abl  staff3  3612970481
695516470  abl  abl  3609276901
695516470  abl  abl  3609276896
739082923  abl  staff3  3866086756
739082923  abl  staff3  3866079064
739082923  abl  abl  3856964132
739082923  abl  staff3  3844779912
739082923  abl  staff3  3916948647
739082923  abl  staff3  3880367425
739082923  abl  abl  3977088222
739082923  abl  staff3  3974513295
739082923  abl  staff3  3973285282
739082923  abl  abl  3960907806
739082923  abl  staff3  4003664468
739082923  abl  abl  4002246444
739082923  abl  staff3  3996089944
739082923  abl  staff3  4024726432
739082923  abl  abl  4021516823
739082923  abl  staff3  4018957318
739082923  abl  abl  4050609080
739082923  abl  staff3  4052368867
739082923  abl  abl  4052424820
739082923  abl  staff3  4053183569

Open in new window

0
PortletPaulfreelancerCommented:
Note you can try changes yourself at sqlfiddle.

I don't really have a way to simulate "this user" (which I presume your application does have by the logged in user identity) So what I did for this was to ignore this where clause:
/* WHERE a_messages2.sender != a_messages2.this_user */

With that change the query result was (for the new sample):
| PROFILE_ID | SENDER | PROFILE_ID_COUNT | MESSAGE_TEXT | CONVERSATION_ID | DATEAGO | MESSAGE_ID | MESSAGE_READ |
|------------|--------|------------------|--------------|-----------------|---------|------------|--------------|
|        abl |    abl |               33 |       (null) |       739082923 |  (null) | 4052424820 |       (null) |
|        abl | staff3 |               33 |       (null) |       739082923 |  (null) | 4053183569 |       (null) |

Open in new window

Full details, including query with that sigle change, below:
    CREATE TABLE `a_messages2` (
           `a_messages_id` INT (11) auto_increment  primary key
         , `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)
         );
    
    CREATE TABLE `search` (
           `search_id` INT (11) auto_increment  primary key
         , `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
         );
    
    
    	
    INSERT INTO a_messages2
    	(`conversation_id`, `profile_id`, `sender`, `message_id`)
    VALUES
    	(695516470, 'abl', 'staff3', 3662615602),
    	(695516470, 'abl', 'abl', 3660687080),
    	(695516470, 'abl', 'staff3', 3658803944),
    	(695516470, 'abl', 'staff3', 3649952930),
    	(695516470, 'abl', 'staff3', 3644250249),
    	(695516470, 'abl', 'staff3', 3614235515),
    	(695516470, 'abl', 'abl', 3614140442),
    	(695516470, 'abl', 'staff3', 3613984751),
    	(695516470, 'abl', 'staff3', 3613936332),
    	(695516470, 'abl', 'abl', 3613469574),
    	(695516470, 'abl', 'staff3', 3612970481),
    	(695516470, 'abl', 'abl', 3609276901),
    	(695516470, 'abl', 'abl', 3609276896),
    	(739082923, 'abl', 'staff3', 3866086756),
    	(739082923, 'abl', 'staff3', 3866079064),
    	(739082923, 'abl', 'abl', 3856964132),
    	(739082923, 'abl', 'staff3', 3844779912),
    	(739082923, 'abl', 'staff3', 3916948647),
    	(739082923, 'abl', 'staff3', 3880367425),
    	(739082923, 'abl', 'abl', 3977088222),
    	(739082923, 'abl', 'staff3', 3974513295),
    	(739082923, 'abl', 'staff3', 3973285282),
    	(739082923, 'abl', 'abl', 3960907806),
    	(739082923, 'abl', 'staff3', 4003664468),
    	(739082923, 'abl', 'abl', 4002246444),
    	(739082923, 'abl', 'staff3', 3996089944),
    	(739082923, 'abl', 'staff3', 4024726432),
    	(739082923, 'abl', 'abl', 4021516823),
    	(739082923, 'abl', 'staff3', 4018957318),
    	(739082923, 'abl', 'abl', 4050609080),
    	(739082923, 'abl', 'staff3', 4052368867),
    	(739082923, 'abl', 'abl', 4052424820),
    	(739082923, 'abl', 'staff3', 4053183569)
    ;

**Query 1**:

    SELECT
            m.profile_id
          , m.sender
          , m.profile_id_count
          , m.message_text
          , m.conversation_id
          , m.dateAgo
          , m.message_id
          , m.message_read
    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
                  , a_messages2.this_user
                  , 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
    
    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 */
    

**[Results][2]**:
    
    | PROFILE_ID | SENDER | PROFILE_ID_COUNT | MESSAGE_TEXT | CONVERSATION_ID | DATEAGO | MESSAGE_ID | MESSAGE_READ |
    |------------|--------|------------------|--------------|-----------------|---------|------------|--------------|
    |        abl |    abl |               33 |       (null) |       739082923 |  (null) | 4052424820 |       (null) |
    |        abl | staff3 |               33 |       (null) |       739082923 |  (null) | 4053183569 |       (null) |

Open in new window

see: http://sqlfiddle.com/#!9/3b413/3
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.

rgb192Author Commented:
In this example there should be no results

because staff3 sent the last message

Staff3 is currently waiting for a response from the customer.
0
rgb192Author Commented:
I don't really have a way to simulate "this user"

for this data, all the columns are (sent by)/ (received by) `staff3`

INSERT INTO a_messages2
          (`conversation_id`, `profile_id`, `sender`, `message_id`, `this_user`)
    VALUES
           (..,..,.,`staff3`)
0
PortletPaulfreelancerCommented:
OK, knowing what you expect makes the difference, below I have used @this_user to simulate the effect of your application - and it produces no rows.
set @this_user := 'staff3';

SELECT
        m.profile_id
      , m.sender
      , m.profile_id_count
      , m.message_text
      , m.conversation_id
      , m.dateAgo
      , m.message_id
      , m.message_read
FROM (
        SELECT
                @row_num := IF(@prev_value = a_messages2.conversation_id, @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
              , a_messages2.this_user
              , mc.profile_id_count
              , @prev_value := a_messages2.conversation_id 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
        ORDER BY a_messages2.conversation_id, a_messages2.message_id DESC /* a_messages2.sender,*/
     ) AS m

WHERE m.RowNumber = 1 /* this will provide "the latest message - per conversation " */
AND m.sender != @this_user

ORDER BY
        m.profile_id
      , m.sender
;

Open in new window

http://sqlfiddle.com/#!9/3b413/16
0
rgb192Author Commented:
  
INSERT INTO a_messages2
          (`conversation_id`, `profile_id`, `sender`, `message_id`, `this_user`)
    VALUES
(695516470, 'abl', 'staff3', 3662615602, 'staff3'),
  (695516470, 'abl', 'abl', 3660687080, 'staff3'),
  (695516470, 'abl', 'staff3', 3658803944, 'staff3'),
  (695516470, 'abl', 'staff3', 3649952930, 'staff3'),
  (695516470, 'abl', 'staff3', 3644250249, 'staff3'),
  (695516470, 'abl', 'staff3', 3614235515, 'staff3'),
  (695516470, 'abl', 'abl', 3614140442, 'staff3'),
  (695516470, 'abl', 'staff3', 3613984751, 'staff3'),
  (695516470, 'abl', 'staff3', 3613936332, 'staff3'),
  (695516470, 'abl', 'abl', 3613469574, 'staff3'),
  (695516470, 'abl', 'staff3', 3612970481, 'staff3'),
  (695516470, 'abl', 'abl', 3609276901, 'staff3'),
  (695516470, 'abl', 'abl', 3609276896, 'staff3'),
  (739082923, 'abl', 'staff3', 3866086756, 'staff3'),
  (739082923, 'abl', 'staff3', 3866079064, 'staff3'),
  (739082923, 'abl', 'abl', 3856964132, 'staff3'),
  (739082923, 'abl', 'staff3', 3844779912, 'staff3'),
  (739082923, 'abl', 'staff3', 3916948647, 'staff3'),
  (739082923, 'abl', 'staff3', 3880367425, 'staff3'),
  (739082923, 'abl', 'abl', 3977088222, 'staff3'),
  (739082923, 'abl', 'staff3', 3974513295, 'staff3'),
  (739082923, 'abl', 'staff3', 3973285282, 'staff3'),
  (739082923, 'abl', 'abl', 3960907806, 'staff3'),
  (739082923, 'abl', 'staff3', 4003664468, 'staff3'),
  (739082923, 'abl', 'abl', 4002246444, 'staff3'),
  (739082923, 'abl', 'staff3', 3996089944, 'staff3'),
  (739082923, 'abl', 'staff3', 4024726432, 'staff3'),
  (739082923, 'abl', 'abl', 4021516823, 'staff3'),
  (739082923, 'abl', 'staff3', 4018957318, 'staff3'),
  (739082923, 'abl', 'abl', 4050609080, 'staff3'),
  (739082923, 'abl', 'staff3', 4052368867, 'staff3'),
  (739082923, 'abl', 'abl', 4052424820, 'staff3'),
  (739082923, 'abl', 'staff3', 4053183569)

Open in new window



The query should return no rows because staff3 is the last user to respond.

Real world example:
Staff3 asks 'Customer do you have any more questions?'

And then I find all the results which need an automatic response.

'Customer do you have any more questions?'
'Customer do you have any more questions?'
'Customer do you have any more questions?'
'Customer do you have any more questions?'
'Customer do you have any more questions?'
'Customer do you have any more questions?'

The customer will not want to get too many responses.
0
PortletPaulfreelancerCommented:
Testing is your responsibility really....

The last query I have provided is different in 2 major ways.

A:
the "partition" is by each conversation now.
So the calculation of RowNumber starts at 1 for the latest message in each conversation.

B:
If the latest message in a conversation has been sent by "this user" then it is not listed
(i.e. only those messages which are sent by somebody else are listed)

---------
I am only working within MySQL
& I "cannot simulate your application"

Your application (perhaps in PHP?) will "know" who is logged in
- by itself my query does not "know" who is logged in
- that is what I mean: I cannot simulate the full context of "this user"

If this does not make sense to you, please describe in words (not more data) how the query is supposed to understand "this user"
0
rgb192Author Commented:
This_user is a staff member 'staff3'

each staff member has a different set of customers


QUERY REQUIREMENTS CHANGED:
conversation_id is not used.
please take conversation_id out of the query
  
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)

Open in new window


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



If the latest message in a conversation has been sent by "this user" then it is not listed

after much thought I have decided not to use conversation_id

I think you solved the query using conversation_id and your answer is correct if I were using conversation id

Real world explanation:
Respond to the customers waiting for a response.
0
PortletPaulfreelancerCommented:
I will leave conversation_id within the logic as it appears needed, I have changed one line in the final where clause (and removed the variable representing "this user" as that concept remains unexplained).
SELECT
        m.profile_id
      , m.sender
      , m.profile_id_count
      , m.message_text
      , m.conversation_id
      , m.dateAgo
      , m.message_id
      , m.message_read
FROM (
        SELECT
                @row_num := IF(@prev_value = a_messages2.conversation_id, @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
              , a_messages2.this_user
              , mc.profile_id_count
              , @prev_value := a_messages2.conversation_id 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
        ORDER BY a_messages2.conversation_id, a_messages2.message_id DESC /* a_messages2.sender,*/
     ) AS m

WHERE m.RowNumber = 1
AND sender = profile_id /* here is the latest change */

ORDER BY
        m.profile_id
      , m.sender
;

Open in new window

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
rgb192Author Commented:
same table insert
  
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)

Open in new window









I do not think that
mc.profile_id_count is needed anymore


please only use these four columns because these are the only 4 columns I use now and delete any references to the other columns
        m.profile_id
      , m.sender
      , m.message_id
      ,this_user


SELECT
        m.profile_id
      , m.sender
      , m.message_id
      ,this_user
FROM (
        SELECT
                @row_num := IF(@prev_value = a_messages2.conversation_id, @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
              , a_messages2.this_user
              , mc.profile_id_count
              , @prev_value := a_messages2.conversation_id 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
        ORDER BY a_messages2.conversation_id, a_messages2.message_id DESC /* a_messages2.sender,*/
     ) AS m

WHERE m.RowNumber = 1
AND sender = profile_id /* here is the latest change */
and profile_id='abl'
ORDER BY
        m.profile_id
      , m.sender
;

Open in new window



abl  abl  4052424820  staff3
abl  abl  4050609080  staff3
abl  abl  3977088222  staff3

Open in new window


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
0
rgb192Author Commented:
You answered the requirement.  Thank you.


This is an easier version of the question after you showed me data.

http://www.experts-exchange.com/Database/MySQL/Q_28280747.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.