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
Year Age RN
2014 30 1
2014 31 2
2014 32 3
2015 25 1
2015 26 2
2015 27 3
2015 28 4
2015 29 5
i want one more addtional column (Desired Output) with max of RN in each group as below"
Year Age RN Desired output
2015 25 1 5
2015 26 2 5
2015 27 3 5
2015 28 4 5
2015 29 5 5
2014 30 1 3
2014 31 2 3
2014 32 3 3
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;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
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
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
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
Year Age RN
2014 30 1
2014 31 2
2014 32 3
2015 25 1
2015 26 2
2015 27 3
2015 28 4
2015 29 5
i want one more addtional column (Desired Output) with max of RN in each group as below"
Year Age RN Desired output
2015 25 1 5
2015 26 2 5
2015 27 3 5
2015 28 4 5
2015 29 5 5
2014 30 1 3
2014 31 2 3
2014 32 3 3
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