January 10, 2013 at 6:29 am
Please find the test data as below.finding tough to come up with logic.
Would appreciate greatly if some could help
create table #data
(
contactid int,
totaldue money,
rn int,
maxrn int
)
select 1, 49846.693, 1, 1
union all
select 1, 43214.9511, 2, 2
union all
select 2, 43962.7901, 1, 1
union all
select 2, 42123.1691, 2, 2
union all
select 3, 89409.6319, 1, 1
union all
select 3, 82078.0355, 2, 2
union all
select 4, 27162.5876, 1, 1
I want the o/p like
contactid,totaldue,rn,maxrn
1,43214,2,2
2,42123.1691,2,2
3,82078.0355,2,2
4,27162.5876,1,1
January 10, 2013 at 6:36 am
A good case for ROW_NUMBER()
drop table #data
create table #data
(
contactid int,
totaldue money,
rn int,
maxrn int
)
insert into #data
select 1, 49846.693, 1, 1
union all
select 1, 43214.9511, 2, 2
union all
select 2, 43962.7901, 1, 1
union all
select 2, 42123.1691, 2, 2
union all
select 3, 89409.6319, 1, 1
union all
select 3, 82078.0355, 2, 2
union all
select 4, 27162.5876, 1, 1
select * from #data
;with cte as
(
select
ROW_NUMBER() OVER(PARTITION BY ContactID ORDER BY RN DESC) AS RowNum,
*
FROM
#data
)
select * from cte where rownum = 1
January 10, 2013 at 6:45 am
Perfect !!! is it possible to come up with some dynamic query here we are selecting the rows based on the row number(rn)
January 10, 2013 at 6:48 am
What do you want to make dynamic?
But yes if you write dynamic SQL then you can make it dynamic.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply