Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2005
    Posts
    5

    Exclamation Unanswered: Select Last Updated Row For Each User Id

    Hi Fellow MySQLers,

    As the title hints at, I am trying to create a query that will select that lastupdated row for each userid. I have two tables that I want to join in the query, one called "sales" and another called "contacts" using the variable "contactid". I would like the results from the query to populate a page for each "sales id" with the last updated information. However, I am having some trouble creating the correct query to get the results I need.

    Currently, I have put together the following query that works:
    $query = "SELECT contacts.company,sales.leadid,sales.service,sales. product,sales.inquirydate,sales.followup,sales.las tupdatedon FROM contacts,sales WHERE contacts.contactid = sales.contactid AND (status = 'Initial Contact' OR status = 'Initial Response') ORDER BY leadid DESC LIMIT $page, $limit";

    The result of this gives me a description of every sales lead. However, I want to be able to show the information in most its up-to-date state only for each sales lead.

    Logic tells me that I want to SELECT my info FROM my tables WHERE MAX(lastupdatedon) FOR EACH LEADID. Of course, my language is not compliant with mysql language. Can anyone offer some assistance? I am sure it is some simple little word I have to add to get it to work. THANKS IN ADVANCE!!!!
    Last edited by canman; 02-20-05 at 07:45.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it's easy if you can use a subquery, a little messier if you cannot

    you didn't say which version you're on
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select contacts.company
         , s1.leadid
         , s1.service
         , s1.product
         , s1.inquirydate
         , s1.followup
         , s1.lastupdatedon 
      from contacts
    inner
      join sales as s1
        on contacts.contactid = s1.contactid 
       and s1.status in ('Initial Contact'
                        ,'Initial Response') 
    inner
      join sales as s2
        on contacts.contactid = s2.contactid 
       and s2.status in ('Initial Contact'
                        ,'Initial Response') 
    group
        by contacts.company
         , s1.leadid
         , s1.service
         , s1.product
         , s1.inquirydate
         , s1.followup
         , s1.lastupdatedon 
    having s1.lastupdatedon
         = max(s2.lastupdatedon)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2005
    Posts
    5
    I think my ISP is running 3.23.58. Thanks for your help and say hello to the TO for me.

  5. #5
    Join Date
    Mar 2004
    Posts
    480
    Tell your ISP they are seriously lagging with the version they are running. As Rudy mentioned in the sticky thread at the top of this list 4.1 has been the full production version since Oct. 2004.

  6. #6
    Join Date
    Feb 2005
    Posts
    5
    Thanks for your help! This is yet another reason for me to move my site now, which I plan on doing in the next month because there is no point for it to be hosted 4000 miles away.

    Anyhoo, I tried the code and it worked...almost.... It returned the necessary info for all but four "leads" each with a unique leadid. I am assuming that this is because each of the missing leads is from the same company and hence shares the same contact id. I have poked around with the code to try and figure out why the four leads are being omitted, but cannot seem to isolate the problem. I guess it has something to do with the GROUP BY or HAVING parts, but can't seem to determine the problem. Any more hints please???

    Thanks!!!

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it doesn't really matter how far away you are from y9our host, what counts is how far away your site visitors are

    for example, i'm in toronto canada, and my site is in phoenix arizona

    your four missing leads might have been dropped either because of the WHERE condition, or because (less likely) they didn't have a valid lastupdatedon value
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2005
    Posts
    5
    I know what you mean, but I also like to have the ability to go talk face-to-face if need be. Currently mine is in Van, but I moved to Hong Kong. Other problem with this is my days is their nights and usually they are fast alseep when I have an issue.

    So I played around with it more and found this, if I insert s2.leadid in the GROUP BY section, then the four leads that share the same contact id as the other that is showing reappear. The code looks like...
    $query = "SELECT contacts.company, s1.leadid, s1.service, s1.product, s1.inquirydate, s1.followup, s1.lastupdatedon FROM contacts INNER JOIN sales AS s1 ON contacts.contactid = s1.contactid AND s1.status IN ('Initial Contact','Initial Response') INNER JOIN sales AS s2 ON contacts.contactid = s2.contactid AND s2.status IN ('Initial Contact','Initial Response') GROUP BY contacts.company, s1.leadid, s1.service, s1.product, s1.inquirydate, s1.followup, s1.lastupdatedon, s2.leadid HAVING s1.lastupdatedon = max(s2.lastupdatedon) ORDER BY s1.leadid DESC LIMIT $page,$limit";

    Any ideas on why this works?

    THANKS!!!!!!!!!!!!

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if your host doesn't have 24/7 support, get a new host

    adding a column to the GROUP BY like you did is not a good idea, unless you really know how grouping works and why there's a self-join in this query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •