Solved

how did you hear about us?

Posted on 2013-11-02
3
447 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
  • 2
3 Comments
 
LVL 48

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Clean text to insert in database 9 60
join tables 4 54
PHP - AJAX and MySQL it works only if the value is a number 12 55
MySQL Query Using Up Memory 6 22
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

806 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