How to choose max of each groups formed by Row_number
-
I have a below table as:
IF OBJECT_ID('tempdb..#Test') IS NOt NULL
DROP TABLe #Test
--===== Create the test table with
create table #Test([Year] float,
Age Int,
)
INSERT INTO #Test
([Year], Age)
SELECT 2015, 25 UNION ALL
SELECT 2015,26 UNION ALL
SELECT 2015,27 UNION ALL
SELECT 2015,28 UNION ALL
SELECT 2015,29 UNION ALL
SELECT 2014,30 UNION ALL
SELECT 2014,31 UNION ALL
SELECT 2014,32
I queried below to get additional column
Select *,row_number() over(partition by [Year] order by Age) as RN from #Test as
YearAgeRN
2014301
2014312
2014323
2015251
2015262
2015273
2015284
2015295
i want one more addtional column (Desired Output) with max of RN in each group as below"
YearAgeRNDesired output
20152515
20152625
20152735
20152845
20152955
20143013
20143123
20143233
Any help?
-
Here's one way:
with rowsas ( select * ,
row_number() over ( partition by [Year] order by Age ) as RN
from #Test
)
select rows.Year ,
rows.Age ,
max(rows2.RN)
from rows
join rows rows2 on rows2.Year = rows.Year
group by rows.Year ,
rows.Age;
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
The easiest way is to simply reverse the order of your ROW_NUMBER... Just sort in DESC order instead of ASC...
-
Jason A. Long (9/15/2015)
The easiest way is to simply reverse the order of your ROW_NUMBER... Just sort in DESC order instead of ASC...I can't see how. Gives this
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
sqlinterset (9/15/2015)
I have a below table as:I queried below to get additional column
Select *,row_number() over(partition by [Year] order by Age) as RN from #Test as
YearAgeRN
2014301
2014312
2014323
2015251
2015262
2015273
2015284
2015295
i want one more addtional column (Desired Output) with max of RN in each group as below"
YearAgeRNDesired output
20152515
20152625
20152735
20152845
20152955
20143013
20143123
20143233
Any help?
How about this:
with basedata as (
select
[Year],
Age,
row_number() over(partition by [Year] order by Age) as RN
from
#Test
)
select
[Year],
Age,
RN,
max(RN) over (partition by [Year]) as MaxRN
from
basedata
order by
[Year] desc,
Age asc;
-
This query can be greatly simplified if you look at it with a different perspective. Instead of the MAX row number within each group, you want a COUNT within each group. Since the row numbers are by definition sequential and they start at 1, the MAX row number and the COUNT will always be the same.
SELECT *, ROW_NUMBER() OVER(PARTITION BY [Year] ORDER BY Age) AS rn,
COUNT(*) OVER(PARTITION BY [Year]) AS cnt
FROM #Test
ORDER BY [Year] DESC, Age
As far as I can tell, this has essentially the same query plan as Lynn's much more complicated query.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
You're right Phil... I looked at the question too fast and didn't pay close enough attention to the desired output. Mia Culpa...
-
drew.allen (9/15/2015)
This query can be greatly simplified if you look at it with a different perspective. Instead of the MAX row number within each group, you want a COUNT within each group. Since the row numbers are by definition sequential and they start at 1, the MAX row number and the COUNT will always be the same.SELECT *, ROW_NUMBER() OVER(PARTITION BY [Year] ORDER BY Age) AS rn,
COUNT(*) OVER(PARTITION BY [Year]) AS cnt
FROM #Test
ORDER BY [Year] DESC, Age
As far as I can tell, this has essentially the same query plan as Lynn's much more complicated query.
Drew
Complicated??? Looks quite simple and readable to me.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply