Providing additional data when selecting distinct rows

Question

I have a table of login events coming from Active Directory. One type of these events are machine logins, which include the IP address of the machine doing the login. This is handy, since it provides a timestamped way to determine what machine was on what IP at a given time. I'm trying to construct a query that'll give me a timestamped list of when a machine first logged in to an IP (thank you DHCP, IP is variable).

The query that just returns the list if IP addresses that machine has held is simple.

SELECT DISTINCT IP
FROM EventStream
WHERE (Machine='$Machine')

I know 'select distinct' is a non-optimal query, which is why I'm looking for something better. This probably includes sub-queries, of which I know very little. Not providing 'Distinct' in this case returns a table with up to 2000 rows, so a lot of data is being selected out and not used.

What I would really like is some way to phrase a query such that I get a time-stamped list of when a machine first showed up on an IP address. I can fake it in code by iterating this query over the results of the first:

SELECT TOP 1 DateTime
FROM EventStream
WHERE (Machine='$Machine' and IP='$IP')
ORDER BY DateTime

I'm pretty sure these two can be combined into a grand-unified-query. Is this possible, or should I stick with application logic to provide what I'm looking for?

Answer 1

Just to confirm, you want to see all the IP addresses the machine has used along with the first time it appeared on each IP address?

If so you should be able to do something like this:

SELECT IP, max(DateTime) as DateTime
FROM EventStream
WHERE Machine='$Machine'
GROUP BY IP

Answer 2

A bit late to the party, but this would do what you want without you needing to first find the IPs and then loop through to find the information you want:

SELECT  Machine,
        IP,
        Date
FROM    (SELECT Machine,
                IP,
                Date,
                ROW_NUMBER() OVER (PARTITION BY Machine, IP ORDER BY Date DESC) RN
         FROM   EventStream) EventStream
WHERE   RN = 1

This gives you all IPs for all Machines in Eventstream and returns only the last date (ORDER BY Date DESC) for each, according to the column Date. If you want the first date, just remove 'DESC' from the order by.