Need Help with Query

Question

Need Help with Query

htt

SSCertifiable

Points: 5242
More actions
October 24, 2008 at 5:34 pm

#214449

Sample data:
create table #a
(ID int identity (1,1)
, AgentID varchar(10)
, TransDate datetime
, AgentType varchar (10))
insert #a (AgentID, TransDate, AgentType)
select 'AAA', '5/7/08', 'Standard'
union
select 'AAA', '5/8/08', 'Standard'
union
select 'BBB', '5/8/08', 'Standard'
union
select 'BBB', '5/9/08', 'Advanced'
union
select 'CCC', '5/9/08', 'Standard'
union
select 'AAA', '6/1/08', 'Advanced'
union
select 'AAA', '7/7/08', 'Standard'
union
select 'CCC', '8/7/08', 'Standard'
I need to get the latest AgentType for each agent. Expected result:
AgentIDAgentType
AAAStandard
BBBAdvanced
CCCStandard
My query below works but I am sure it doesn't look professional:
select AgentID, AgentType from #a where AgentID + cast(TransDate as varchar (30)) in
(select distinct AgentID + cast(max(TransDate) as varchar (30)) from #a group by AgentID)
Is there another query producing exprected result?
Regards,

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

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

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 1

You can use a common table expression to store the agentid with the max date and then join it back to the original table to limit the results.

create table #a

(ID int identity (1,1)

, AgentID varchar(10)

, TransDate datetime

, AgentType varchar (10))

insert #a (AgentID, TransDate, AgentType)

select 'AAA', '5/7/08', 'Standard'

union

select 'AAA', '5/8/08', 'Standard'

union

select 'BBB', '5/8/08', 'Standard'

union

select 'BBB', '5/9/08', 'Advanced'

union

select 'CCC', '5/9/08', 'Standard'

union

select 'AAA', '6/1/08', 'Advanced'

union

select 'AAA', '7/7/08', 'Standard'

union

select 'CCC', '8/7/08', 'Standard'

;

With cteMaxTranDate

AS

(

select AgentID, max(TransDate) MaxTransDate

from #a group by AgentID

)

select #a.AgentID, #a.AgentType

from #a Join

cteMaxTranDate ON

#a.AgentID = cteMaxTranDate.AgentID AND

#a.TransDate = cteMaxTranDate.MaxTransDate

Drop Table #a

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

Doing it the old fashioned way using a "Derived table" instead of a CTE...

SELECT orig.AgentID, orig.AgentType

FROM #a orig

INNER JOIN

(SELECT AgentID, MAX(TransDate) AS MaxTransDate FROM #a GROUP BY AgentID) maxdate

ON Orig.AgentID = maxdate.AgentID

AND orig.TransDate = maxdate.MaxTransDate

ORDER BY orig.AgentID

Note that you do not need any date conversions for this or the CTE version that Ken wrote. They just slow things down.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

htt SSCertifiable Points: 5242 More actions · Answer 3

Thanks!

I also found this qurey worked for me:

select a.AgentID, a.AgentType from

(select AgentID, row_number() over (partition by AgentID order by TransDate desc) as row, TransDate, AgentType from #a) a

where a.row = 1

I am kind of behind so there are new things in SQL 2005 I haven't learned and used :blink:

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

You don't need to include TransDate in that... just wasted IO and clock cycles. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)