Join 2 Tables With Latest Record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Join 2 Tables With Latest Record

Author

Topic

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2012-01-05 : 17:02:59

I have a query like this below -

select u.username as [account name], ap.applicationname as [applicationdate], ud.lastupdate
from users u, userapplicationdata ud
inner join applications ap on ud.applicationid = ap.applicationid
order by [account name]asc, [application name] asc

This query will bring back multiple records of somsone so for example.

Joe Jones, Google, 2012-01-01 19:432
Joe Jones, Google, 2012-01-02 19:432
Joe Jones, Google, 2012-01-05 19:432
Joe Jones, Google, 2012-01-08 19:432
Joe Jones, Google 2, 2012-01-08 19:432
Joe Jones, Google 2, 2012-01-06 19:432
Frank Jones, Google, 2012-01-08 19:432
Frank Jones, Google, 2012-01-09 19:432

I would like for it to look at each "set" of users records of a particlular application, and find the one with the latest date, and didsplay that only, so the result would be like.

Joe Jones, Google, 2012-01-08 19:432
Joe Jones, Google 2, 2012-01-08 19:432
Frank Jones, Google, 2012-01-09 19:432

any ideas?

pduffin
Yak Posting Veteran

68 Posts

Posted - 2012-01-05 : 17:17:55

This is something I wrote for a previous post to show how to do this, I think this will work for you too!

create table #tmpCustomer (customerid int, customername char(50))
create table #tmpCustomerCall (customerid int, calldatetime datetime, callresultcode int)

insert into #tmpCustomer values (1,'bob')
insert into #tmpCustomer values (2,'jeff')

insert into #tmpCustomerCall values (1,'jan 01,11',100)
insert into #tmpCustomerCall values (1,'jan 05,11',101)
insert into #tmpCustomerCall values (1,'feb 05,11',101)

insert into #tmpCustomerCall values (2,'feb 01,11',100)
insert into #tmpCustomerCall values (2,'mar 05,11',101)
insert into #tmpCustomerCall values (2,'apr 05,11',103)

select cu.customerid,customername,calldatetime,callresultcode
from #tmpcustomer cu
join #tmpcustomercall ca on ca.customerid = cu.customerid
where ca.calldatetime =
(
select max(calldatetime) calldatetime
from #tmpcustomercall cc
where cc.customerid = cu.customerid
)

Likes to run, hates the runs!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-06 : 13:51:43

[code]
select [account name], [applicationdate], lastupdate
from
(
select u.username as [account name], ap.applicationname as [applicationdate], ud.lastupdate,row_number() over (partition by u.username , ap.applicationname order by ud.lastupdate desc) as Rn
from users u, userapplicationdata ud
inner join applications ap on ud.applicationid = ap.applicationid
)t
where rn=1
order by [account name]asc, [application name] asc
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-06 : 13:52:40

just noticed in your query you dont have any relationship specified between users and userapplicationdata tables so its effectively doing a cross join. is this intentional?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources