Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2005
    Posts
    6

    Unanswered: Need Help DISTINCT

    I need help aggregating unique data.

    Let's say my table consists of the following data:
    ID COMPANYNAME COMPANYNUMBER UpdatedGMT

    AF5AD27D CompanyA 987654321XX 1/12/2010 6:40:26 PM
    A4FD4F65 CompanyB 32748923 11/18/2010 6:45:30 PM
    AF5AD27D CompanyA 987654321ZZ 11/18/2010 6:47:29 PM

    I need to pull both the CompanyName and CompanyNumber based on the last Update to each, this is stored in UpdatedGMT.

    So my expected result should be

    CompanyA 987654321ZZ
    CompanyB 32748923

    Because I only want to grab the lasest record for the updated Company A

    Please help ASAP!

    Thank you sooo much.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT t.companyname
         , t.companynumber
      FROM ( SELECT companyname
                  , MAX(updatedgmt) AS max_time
               FROM daTable
             GROUP
                 BY companyname ) AS m
    INNER
      JOIN daTable AS t
        ON t.companyname = m.companyname
       AND t.updatedgmt = m.max_time
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2010
    Posts
    335
    select COMPANYNAME, COMPANYNUMBER
    from <table> t1
    where not exists
    (select t2.UpdatedGMT from <table> t2
    where t2.UpdatedGMT > t1.UpdatedGMT and
    t1.COMPANYNAME = t2.COMPANYNAME)

    This should work, but there's maybe a faster way.

  4. #4
    Join Date
    Sep 2005
    Posts
    6
    Thanks for the replies.

    A couple things that probably would have been helpful.

    First, the company can vary to, so i can't join on that. CompanyA can also be updated to Company_A, thus we need to key off of the ID.

    Second the table is not a physical table but a result set I am aliasing (sp?) as a table...

    ie- SELECT ID, CompanyName, CompanyNumber, UpdatedGMT FROM (Select id, companyname, companynumber, effectivedate, updatedgmt from table join table on etc, etc, etc) AS X

    Hope this helps.

    I couldnt get either suggestion to work- probably due to these ommissions, my apologies.

    Thanks

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pjf135 View Post
    ... thus we need to key off of the ID.
    Code:
    SELECT t.id
         , t.companyname
         , t.companynumber
      FROM ( SELECT id
                  , MAX(updatedgmt) AS max_time
               FROM daTable
             GROUP
                 BY id) AS m
    INNER
      JOIN daTable AS t
        ON t.id= m.id
       AND t.updatedgmt = m.max_time
    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
  •