Need Help DISTINCT

pjf135 · #1 (**permalink**) 12-01-10, 10:34

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.

r937 · #2 (**permalink**) 12-01-10, 10:45

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

nvk@vhv · #3 (**permalink**) 12-01-10, 10:52

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.

pjf135 · #4 (**permalink**) 12-01-10, 12:11

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

r937 · #5 (**permalink**) 12-01-10, 14:20

Quote:

Originally Posted by pjf135

... 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

Internet Services

Web Development

Digital Marketing

Consumer Tech