Ranking help for some numeric field data

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

SQL Server 2005 Forums

Transact-SQL (2005)

Ranking help for some numeric field data

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2013-11-27 : 03:05:56

I have a following query giving result as below

Select *
From (Select TemplateSectionId
, FkTemplateId
, SectionOrder
, Count(1)
Over (
PARTITION By FkTemplateId) As Cnt
From dbo.TemplateSection) ts
Where ts.FkTemplateId = 22

TemplateSectionId FkTemplateId SectionOrder Cnt
49 22 90 2
52 22 2 2

in above result section order are 90 ,21 and 2

What i want's is sectionRank like 3rd for 90,2nd for 21 and 1st for 2
like

TemplateSectionId FkTemplateId SectionOrder Cnt SectionRank
49 22 90 2 3
53 22 21 2 2
52 22 2 2 1

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2013-11-27 : 05:16:41

Solve it via ROW_NUMBER()Over(partition by FkTemplateId Order by SectionOrder asc

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-27 : 05:18:25

[code]
Select *
From (Select TemplateSectionId
, FkTemplateId
, SectionOrder
, Count(1)
Over (
PARTITION By FkTemplateId) As Cnt
, DENSE_RANK() Over (PARTITION By FkTemplateId ORDER BY SectionOrder ASC) As SectionRank
From dbo.TemplateSection) ts
Where ts.FkTemplateId = 22
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-27 : 05:20:01

quote:
Originally posted by kamii47

Solve it via ROW_NUMBER()Over(partition by FkTemplateId Order by SectionOrder asc

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

will work correctly so long as SectionOrder doesnt repeat within a FkTemplateId
More safer option is to use DENSE_RANK as i showed

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources