August 5, 2010 at 1:37 pm
I need to select the last row of a table after grouping. I have something that works, but it seems there should be a simpler way to do this..
IF OBJECT_ID(N'tempdb..#t1', N'U') IS NOT NULL
DROP TABLE #t1;
GO
create table #t1 (
SiteStatusStart datetime NULL,
site_key int not null,
p_key int NOT NULL,
SiteStatus varchar(16) NOT NULL,
SiteType varchar(16) NOT NULL,
SiteVR_key int)
insert into #t1 values('2008-12-03', '10', '412', 'Active','Prime','31461')
insert into #t1 values('2006-06-14','20','787','Active','Prime','13947')
insert into #t1 values('2006-07-14','8','787','Active','Prime','14284')
insert into #t1 values('2006-07-18','8','787','Active','Prime','14303')
insert into #t1 values('2008-06-10','8','787','Active','Prime','25595')
insert into #t1 values('2008-06-19','10','787','Active','Prime','26095')
insert into #t1 values('2006-09-22','20','961','Active','Prime','15270')
insert into #t1 values('2005-08-26','22','1001','Active','Prime Only','7901')
insert into #t1 values('2005-09-02','29','1001','Active','Prime','7960')
insert into #t1 values('2006-09-08','20','1001','Active','Prime','14898')
insert into #t1 values('2006-11-09','20','1001','Active','Prime','16005')
insert into #t1 values('2006-11-09','20','1001','Active','Prime','16086')
insert into #t1 values('2007-05-09','20','1001','Site wont use','Prime','18439')
insert into #t1 values('2008-09-25','10','1001','Removed ','Prime','29037')
insert into #t1 values('2005-12-22','20','1176','Active','Prime Only','9286')
insert into #t1 values('2006-01-20','20','1176','Active','Prime Only','10165')
insert into #t1 values('2006-02-22','20','1176','Removed ','Prime','11689')
insert into #t1 values('2006-05-25','20','1176','Active','Prime','13755')
insert into #t1 values('2006-05-25','20','151', 'Removed ','Prime','13756')
insert into #t1 values('2008-08-12','20','1176','Active','Prime','27834')
insert into #t1 values('2009-04-24','10','1176','Site wont use ','Prime','34503')
insert into #t1 values('2005-08-26','20','1312','Active','Prime Only','7908')
insert into #t1 values('2005-09-01','20','1312','Removed ','Prime Only','7956')
insert into #t1 values('2005-09-02','25','1312','Active','Prime Only','7988')
insert into #t1 values('2005-09-07','10','1312','Removed ','Prime','8008')
select * from #t1 order by site_key, p_key, SiteVR_key
select B.*
from (SELECT site_key, p_key
,max(SiteVR_key) as maxID
from #t1
group BY site_key, p_key) as A
inner join #t1 as B
on B.SiteVR_key = A.maxID
order by B.site_key, B.p_key
August 5, 2010 at 1:48 pm
Try this:
;with cte as
(
select row_number() over ( partition by site_key, p_key order by SiteVR_key desc ) RN ,*
from #t1
)
select * from cte where rn = 1
August 6, 2010 at 4:19 am
Thanks. That is faster and cleaner than my code.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply