Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

how did you hear about us?

Posted on 2013-11-02
3
Medium Priority
?
456 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 2000 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

597 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