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.
| 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
|
|
|
|
|
|
|
Ranking help for some numeric field data