Selecting Distinct and Returning In Order

I currently have the below code.  I want to be able to return only one row based on agentGuid which is now working, but I want it to return the newest record for that agentGuid based on the eventTime field.

SELECT     TOP (100) PERCENT MAX(agentGuid) AS Expr1, MAX(result) AS Expr2, MAX(statusType) AS Expr3, MAX(statusType) AS Expr4, MAX(description)
                      AS Expr5, MAX(eventTime) AS Expr6
FROM         dbo.backupLog
GROUP BY agentGuid
jonathanbyersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
Something like this perhaps:
SELECT	bl.*
FROM	dbo.backupLog bl
	INNER JOIN (
		SELECT	agentGuid,
			MAX(eventTime) AS LasteventTime
		FROM    dbo.backupLog
		GROUP BY 
			agentGuid) x ON bl.agentGuid = x.agentGuid AND bl.eventTime = x.LasteventTime

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David ToddSenior DBACommented:
Hi,

Some sample data would be nice ...

HTH
  David
--== script to illustrate answer
if object_id( N'tempdb..#BackupLog', N'U' ) is not null 
	drop table #BackupLog;
	
create table #BackupLog(
	agentGuid uniqueidentifier
	, result int
	, statusType int
	, description varchar( 200 )
	, eventTime datetime
	)
;

print 'insert some random data'
insert #BackupLog( AgentGuid, result, eventTime ) values( '6DE5003C-26F0-44E8-834A-3B1CB4AF436C',  1, '2012-01-07 14:38:25.873' )
insert #BackupLog( AgentGuid, result, eventTime ) values( 'BDBF538F-6394-466F-83E8-ECB6F268235F',  2, '2012-01-07 14:38:26.917' )
insert #BackupLog( AgentGuid, result, eventTime ) values( '2CE71A60-CEBD-4B1A-B070-2BCAFBB0BC65',  3, '2012-01-07 14:38:27.920' )
insert #BackupLog( AgentGuid, result, eventTime ) values( '6DE5003C-26F0-44E8-834A-3B1CB4AF436C',  4, '2012-01-07 14:38:28.923' )
insert #BackupLog( AgentGuid, result, eventTime ) values( 'FEDEF9FA-BF64-4046-A17A-56BFFB716D2E',  5, '2012-01-07 14:38:29.923' )
insert #BackupLog( AgentGuid, result, eventTime ) values( '2DA8F4CF-E57F-4CD1-9BCC-7EB952FA3591',  6, '2012-01-07 14:38:30.927' )
insert #BackupLog( AgentGuid, result, eventTime ) values( 'BDBF538F-6394-466F-83E8-ECB6F268235F',  7, '2012-01-07 14:38:31.930' )
insert #BackupLog( AgentGuid, result, eventTime ) values( '2CE71A60-CEBD-4B1A-B070-2BCAFBB0BC65',  8, '2012-01-07 14:38:32.930' )
insert #BackupLog( AgentGuid, result, eventTime ) values( 'FEDEF9FA-BF64-4046-A17A-56BFFB716D2E',  9, '2012-01-07 14:38:33.933' )
insert #BackupLog( AgentGuid, result, eventTime ) values( '6DE5003C-26F0-44E8-834A-3B1CB4AF436C', 10, '2012-01-07 14:38:34.937' )
insert #BackupLog( AgentGuid, result, eventTime ) values( 'BDBF538F-6394-466F-83E8-ECB6F268235F', 11, '2012-01-07 14:38:35.940' )
insert #BackupLog( AgentGuid, result, eventTime ) values( '6DE5003C-26F0-44E8-834A-3B1CB4AF436C', 12, '2012-01-07 14:38:36.943' )
insert #BackupLog( AgentGuid, result, eventTime ) values( '2DA8F4CF-E57F-4CD1-9BCC-7EB952FA3591', 13, '2012-01-07 14:38:37.950' )
insert #BackupLog( AgentGuid, result, eventTime ) values( '2CE71A60-CEBD-4B1A-B070-2BCAFBB0BC65', 14, '2012-01-07 14:38:38.953' )
insert #BackupLog( AgentGuid, result, eventTime ) values( 'BDBF538F-6394-466F-83E8-ECB6F268235F', 15, '2012-01-07 14:38:39.957' )
insert #BackupLog( AgentGuid, result, eventTime ) values( '6DE5003C-26F0-44E8-834A-3B1CB4AF436C', 16, '2012-01-07 14:38:40.960' )
insert #BackupLog( AgentGuid, result, eventTime ) values( '2CE71A60-CEBD-4B1A-B070-2BCAFBB0BC65', 17, '2012-01-07 14:38:41.960' )
insert #BackupLog( AgentGuid, result, eventTime ) values( 'FEDEF9FA-BF64-4046-A17A-56BFFB716D2E', 18, '2012-01-07 14:38:42.963' )
insert #BackupLog( AgentGuid, result, eventTime ) values( '6DE5003C-26F0-44E8-834A-3B1CB4AF436C', 19, '2012-01-07 14:38:43.967' )
insert #BackupLog( AgentGuid, result, eventTime ) values( 'FEDEF9FA-BF64-4046-A17A-56BFFB716D2E', 20, '2012-01-07 14:38:44.970' )

print 'Results'
select 
	agentGuid
	, max( eventTime ) as max_eventTime
from #BackupLog
group by
	agentGuid
order by
	agentGuid
;

print 'Full row'
select o.*
from #BackupLog o
inner join (
	select 
		agentGuid
		, max( eventTime ) as max_eventTime
	from #BackupLog
	group by
		agentGuid
) i
	on i.agentGuid = o.agentGuid
	and i.max_eventTime = o.eventTime
;

Open in new window

0
David ToddSenior DBACommented:
Hi,

Code I used to generate the sample data. Takes 20 seconds to run due to using waitfor and getdate() and go 20

HTH
  David
--== Generate sample data
/*
-- Askers code

SELECT     TOP (100) PERCENT MAX(agentGuid) AS Expr1, MAX(result) AS Expr2, MAX(statusType) AS Expr3, MAX(statusType) AS Expr4, MAX(description) 
                      AS Expr5, MAX(eventTime) AS Expr6
FROM         dbo.backupLog
GROUP BY agentGuid 

*/

if object_id( N'tempdb..#Agent', N'U' ) is not null 
	drop table #Agent;
	
create table #Agent(
	agentGuid uniqueidentifier
	)
;
go

insert #Agent select newid()
go 5

if object_id( N'tempdb..#BackupLog', N'U' ) is not null 
	drop table #BackupLog;
	
create table #BackupLog(
	agentGuid uniqueidentifier
	, result int
	, statusType int
	, description varchar( 200 )
	, eventTime datetime
	)
;
go

insert #BackupLog(
	agentGuid
	, eventTime
	)
	select top 1 agentGuid, getdate()
	from #Agent
	-- using newid() to randomise #Agent
	order by
		newid()
;

waitfor delay '00:00:01'

go 20
	
select *
from #BackupLog
;

go

select 'insert #BackupLog( AgentGuid, eventTime ) values( ''' + convert( varchar( 40 ), agentGuid ) + ''', ''' + convert( varchar( 30 ), eventTime, 121 ) + ''' )' from #BackupLog

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

SharathData EngineerCommented:
try this.
SELECT * 
FROM   (SELECT *, 
               ROW_NUMBER() OVER (PARTITION BY agentGuid ORDER BY eventTime DESC) rn 
        FROM   dbo.backupLog) T1 
WHERE  rn = 1 

Open in new window

0
Aaron TomoskySD-WAN SimplifiedCommented:
What about:
Declare @agentguid varchar(100);
Select top (1) @agentguid=agentguid from dbo.backuplog order by eventtime desc;
Select * from dbo.backuplog where agentguid = @agentguid;
0
jonathanbyersAuthor Commented:
Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.