Query Fields with the latest timestamp for each item

Question

Query Fields with the latest timestamp for each item

Newbie

Points: 3

May 8, 2006 at 4:46 pm

Help... I'm not a SQL expert and I need to query a table to find the latest recored (LastOnNetwork) for each unique ComputerName.

The table looks like this.

IP	HostName	CompName	DOMAIN	UserName	MAC	LastOnNetwork	DateDiscovered
111.222.101.10	comp1.domain.com	COMP1	DOMAIN	USER2	00-00-00-00-00-01	1/28/2005 11:43	1/1/01
111.222.101.10	comp1.domain.com	COMP1	DOMAIN	USER4	00-00-00-00-00-03	11/8/2005 15:42	10/24/05
111.222.101.1	comp2.domain.com	COMP2	DOMAIN	USER3	00-00-00-00-00-02	4/20/2006 15:42	12/5/05
111.222.101.2	comp2.domain.com	COMP2	DOMAIN	USER1	00-00-00-00-00-00	5/8/2006 11:52	1/5/06

I tried the query below, but all I can't get is:

_________________

IP	HostName	CompName	DOMAIN	UserName	MAC	LastOnNetwork	DateDiscovered
111.222.101.2	comp2.domain.com	COMP2	DOMAIN	USER1	00-00-00-00-00-00	5/8/2006 11:52	1/5/06

SELECT TOP 100 PERCENT IPaddress, Username, MAC, LastSeen, DiscoveryDate, ComputerName

FROM dbo.IPsweep_Historical

WHERE (LastSeen =

(SELECT MAX(lastseen)

FROM IPsweep_Historical))

GROUP BY ComputerName, LastSeen, IPaddress, Username, MAC, DiscoveryDate

ORDER BY ComputerName

________________

The output should be this:

IP	HostName	CompName	DOMAIN	UserName	MAC	LastOnNetwork	DateDiscovered
111.222.101.10	comp1.domain.com	COMP1	DOMAIN	USER4	00-00-00-00-00-03	11/8/2005 15:42	10/24/05
111.222.101.2	comp2.domain.com	COMP2	DOMAIN	USER1	00-00-00-00-00-00	5/8/2006 11:52	1/5/06

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Pam Brisjar SSChampion Points: 12094 More actions · Answer 1

This should work:

SELECT IPaddress, Username, MAC, LastSeen, DiscoveryDate, ComputerName

FROM dbo.IPsweep_Historical ih

INNER JOIN

(SELECT ComputerName, MAX(lastseen) as MaxLast

FROM IPsweep_Historical

GROUP BY ComputerName) ml

ON ih.ComputerName = ml.ComputerName

AND ih.LastSeen = ml.MaxLast

ORDER BY ih.ComputerName

Here's my test:

create table #tmp

(nme varchar(20),

dte datetime,

val1 char(1),

val2 int)

insert into #tmp values ('test1', '01/01/2006','A',1)

insert into #tmp values ('test1', '02/02/2006','B',2)

insert into #tmp values ('test2', '01/01/2006','A',1)

insert into #tmp values ('test2', '02/04/2006','b',2)

insert into #tmp values ('test2', '01/01/2006','C',3)

insert into #tmp values ('test3', '01/01/2006','A',1)

select t.* from #tmp t

inner join

(select nme, MAX(dte) as themax from #tmp group by nme) tp

on t.nme = tp.nme and

t.dte = tp.themax

ORDER BY t.nme

drop table #tmp

Sergiy SSC Guru Points: 110208 More actions · Answer 2

Did you or anybody around heard something about data normalisation, some normal forms?

Difficulties with queries are just indicators of wrong design.

_____________
Code for TallyGenerator

John Snell-326270 Newbie Points: 3 More actions · Answer 3

If you knew how I received the data, you wouldn't be so quick to be critical... Not only that, I'm a security expert not a DBA....

John Snell-326270 Newbie Points: 3 More actions · Answer 4

John Snell-326270

Newbie

Points: 3

May 8, 2006 at 7:13 pm

#636642

You ROCK girl!!! Thanks much Pam!