Solved

how did you hear about us?

Posted on 2013-11-02
3
453 Views
Last Modified: 2013-11-07
I think this question may be related to
http://www.experts-exchange.com/Database/MySQL/Q_28280747.html


I want either the field (profile_id)  or the row of the answer.

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




Real world explanation:
find customers the who messaged us first

Ask them questions such as 'how did you hear about us: radio, tv, internet, friend'

Because these customers did not hear about us from us, because they sent us the first message.


select profile_id, sender,message_id,this_user from a_messages2 where profile_id='abl' order by message_id limit 1

select profile_id, sender,message_id,this_user from a_messages2 where profile_id='swa' order by message_id limit 1

 staff3 sent first message to swa  '4142884976'


abl sent first message to staff3 '3609276896'

there should be results for abl because abl sent first message
but there should be no results for swa, because we sent the first message
0
Comment
Question by:rgb192
[X]
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
  • 2
3 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39619770
Locate the minimum message_id per profile_id, match back to messages by message id and then only list those where the sender equals the profile_id
| SENDER | MESSAGE_ID |
|--------|------------|
|    abl | 3609276896 |

Open in new window

produced by the following query:
SELECT
        m.sender
      , m.message_id
FROM (
      SELECT
        profile_id
      , min(message_id) AS message_id
      FROM a_messages2
      GROUP BY
        profile_id
     ) AS mn
INNER JOIN a_messages2 AS m ON mn.message_id =  m.message_id
WHERE m.profile_id = m.sender
;

/* http://sqlfiddle.com/#!9/8efe9/1 */

Open in new window

0
 

Author Closing Comment

by:rgb192
ID: 39619858
You answered the question correctly with the data provided but with my data some profiles I have written first got returned.

So I would want to modify this query to show me more information so I can give you more data.
0
 

Author Comment

by:rgb192
ID: 39631021
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

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…
Creating and Managing Databases with phpMyAdmin in cPanel.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

687 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