asked on

new data to test with existing query

https://www.experts-exchange.com/questions/28265673/want-to-return-a-list-of-profile-id-which-is-username-of-users-that-are-waiting-for-me-to-respond-to-them.html?anchorAnswerId=39569988#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

PortletPaul

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

rgb192

ASKER

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

PortletPaul

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

rgb192

ASKER

In this example there should be no results

because staff3 sent the last message

Staff3 is currently waiting for a response from the customer.

rgb192

ASKER

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`)

PortletPaul

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

rgb192

ASKER

  
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.

PortletPaul

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"

rgb192

ASKER

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.

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rgb192

ASKER

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

rgb192

ASKER

You answered the requirement. Thank you.

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

https://www.experts-exchange.com/questions/28280747/indicate-row-or-profile-id-which-needs-to-be-responded-to.html