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)

Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -