SELECT
  a.ID,
  a.IP,
  b.Location,
  b.Timestamp
FROM
  IP a,
  (
    SELECT
      Location,
      MAX(Creation_Timestamp) AS Timestamp
    FROM
      IP
    GROUP BY
      Location ) b
WHERE
  a.location = b.location and
  a.creation_timestamp = b.timestamp
ORDER BY
  a.ID

---- Original Message ----
From: salmo@i...
To: sql_language@p..., 
Subject: RE: [sql_language] Re: $100 Reward - Correlated Subquery? To
Obtain Completely Unique Rows
Date: Tue, 22 May 2001 20:56:53 -0700

>
>----- Original Message -----
>From: "Henry Blake" <Somebody2000@h...>
>To: "sql language" <sql_language@p...>
>Sent: Sunday, June 03, 2001 12:22 AM
>Subject: [sql_language] $100 Reward - Correlated Subquery? To Obtain
>Completely Unique Rows
>
>
>> Yes, I mean it! I will personally send you a check for $100.00 to 
>the
>> person that gives me the correct query (type it out please)to 
>solve this
>> problem.
>>
>> Here is my "Log" Table, which has these 4 columns:
>>
>> ID   IP Address        Creation_Timestamp   Location
>>
>> 1    123.123.123.123   1:30pm                Denver
>> 2    123.123.123.123   1:33pm                Denver
>> 3    111.111.111.111   1:45pm                LA
>> 4    111.111.111.111   1:46pm                LA
>> 5    111.0.0.0         1:49pm                Seattle
>>
>> Now the quesiton is....I'm looking to get returned to me ONLY 
>unique IP
>> address's for ONLY the latest time.
>>
>> i.e. I NEED my result set to look like:
>>
>> 2     123.123.123.123   1:33pm    Denver
>> 4     111.111.111.111   1:46pm    LA
>> 5     111.0.0.0         1:49pm    Seattle
>>
>> Thank you!!
>> One Hurt Puppy, Uncle !!
>> I'll send you an email asking for a mailing address or Paypal.

Brian Matsik
President and Senior Consultant
OOCS
-----
Author: ?MCSD Fast Track: Solutions Architecture?, New Riders Press
Co-Author: ?Professional ADO 2.5 Programming?, Wrox Press
Co-Author: ?VBScript Programmer?s Reference?, Wrox Press
Co-Author: ?Professional Visual Interdev 6 Programming?, Wrox Press
Co-Author: ?Professional Access 2000 Programming?, Wrox Press
Co-Author: ?Beginning SQL?, Wrox Press