SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Ranking Functions


SQL Server Ranking Functions

Author
Message
busasquatch
busasquatch
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 31
So, I've overcome the inability to display columnar formats in SSRS subreports by using ROW_NUMBER() along with some modulus "hack-a-math". Here's a brief use case.

Let's say that a customer can have one or more addresses. Using an SSRS subreport, you'd waste a lot of paper displaying these in one column. But, if you could give each address a "RowNumber" and "ColumnNumber", you could drop the addresses into a matrix using RowNumber and ColumnNumber as your groupings.

This revelation, single handedly, was my greatest self-discovery in SQL code in conjunction with SSRS, if I do say so myself. :-D

-------------------
Brian Zive
Assistant Director, Systems
Business Intelligence Analyst
Massachusetts General Hospital
Development Office

___________________________________
Brian A. Zive
Assistant Director, Systems
Business Intelligence Analyst
Massachusetts General Hospital
Development Office
Thomas LeBlanc
Thomas LeBlanc
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4582 Visits: 915
Great article, thanks for writing it and answering people's questions.

ThomasLL

Thomas LeBlanc, MCITP DBA 2005, 2008 & MCDBA 2000
http://thesmilingdba.blogspot.com/
magarity kerns
magarity kerns
SSC Eights!
SSC Eights! (902 reputation)SSC Eights! (902 reputation)SSC Eights! (902 reputation)SSC Eights! (902 reputation)SSC Eights! (902 reputation)SSC Eights! (902 reputation)SSC Eights! (902 reputation)SSC Eights! (902 reputation)

Group: General Forum Members
Points: 902 Visits: 397
I see these functions in Oracle queries all the time and only partly understood what was going on - thanks for explaining it the SQL Server way! Now I can translate the Oracleisms!
Trey Staker
Trey Staker
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1602 Visits: 2788
Wayne, Great article! It came a long ways. Smile

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
WayneS
WayneS
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21168 Visits: 10652
Jeff Moden (4/19/2010)
Yowch... the formatting monster hit the code hard on this one. I hope Steve can fix it soon.

I think it's all fixed now... all the code is in one block, and missing spaces have been added back in. Sure makes it a lot easier to read now. Thanks Steve!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

UMG Developer
UMG Developer
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3970 Visits: 2204
Thanks Wayne, I had been meaning to investigate the ranking/window functions but hadn't gotten the time yet. Your article with the clear examples made it a cinch to understand the differences.
matt stockham
matt stockham
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2564 Visits: 3178
WayneS (4/20/2010)
SW_Lindsay (4/20/2010)
Nice and helpful article - Thanks. Just Curious...

To select eligible candidates in the first select you say

select * from @Candidates where MeetsEligibility = convert(bit,1);

Why do you convert the 1 into a bit? just saying 1 works. I know that the data type for MeetsEligibility is a bit and I'm just curious if there are efficiencies is converting explicitly like this or is it just a readability thing?


Steve

Avoiding an "Implicit conversion". The literal 1 is an integer, resulting in the underlying field being converted to an integer to do the match... if there is an index on this field, it won't be used. Converting the 1 to a bit avoids the implicit conversion, and allows use of an index if one is present.

Personally, I wish there were system variables @@True and @@False of datatype bit, set to 1/0 respectively.



You can use 'TRUE' and 'FALSE' as quoted strings - http://msdn.microsoft.com/en-us/library/ms177603.aspx

Your implicit conversion comment is incorrect - as I understand it, the column is treated like any exact numeric from a query perspective. An index will be used if appropriate. That will primarily be determined by whether other non-columns are required (as in your example) and by the selectivity of the index.

Matt.
WayneS
WayneS
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21168 Visits: 10652
matt stockham (4/20/2010)
You can use 'TRUE' and 'FALSE' as quoted strings - http://msdn.microsoft.com/en-us/library/ms177603.aspx

I didn't know this... just tried it out, and it does work.

Your implicit conversion comment is incorrect - as I understand it, the column is treated like any exact numeric from a query perspective. An index will be used if appropriate. That will primarily be determined by whether other non-columns are required (as in your example) and by the selectivity of the index.

Matt.


I could have sworn that I had seen this happen before, but I can't duplicate it. Using 'TRUE', 'FALSE', 1, 0, or a 1/0 cast into an int variable all are producing an index seek for me, so I guess it isn't necessary.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

UMG Developer
UMG Developer
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3970 Visits: 2204
matt stockham (4/20/2010)
You can use 'TRUE' and 'FALSE' as quoted strings - http://msdn.microsoft.com/en-us/library/ms177603.aspx


Matt,

Thanks for some reason I never saw that, I think that will help make some code more readable.
SW_Lindsay
SW_Lindsay
SSC-Addicted
SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)

Group: General Forum Members
Points: 455 Visits: 311
Thanks to all this is why I subscibe to learn something and I learned a lot from this article and subsequent discussion.



steve
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search