SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Ranking help for some numeric field data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kamii47
Constraint Violating Yak Guru

352 Posts

Posted - 11/27/2013 :  03:05:56  Show Profile  Reply with Quote
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

352 Posts

Posted - 11/27/2013 :  05:16:41  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 11/27/2013 :  05:18:25  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/27/2013 :  05:20:01  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000