Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Wayne Sheffield » SQL Server Ranking Functions
Add to briefcase
31 posts, Page 2 of 4
««1234»»»

SQL Server Ranking Functions Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
busasquatch
Posted Tuesday, April 20, 2010 8:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 30, 2013 3:00 PM
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.

-------------------
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
Post #906861
Thomas LeBlanc
Posted Tuesday, April 20, 2010 8:44 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 20, 2013 3:33 PM
Points: 2,706, Visits: 717
Great article, thanks for writing it and answering people's questions.

ThomasLL

Thomas LeBlanc, MCITP DBA 2005, 2008 & MCDBA 2000
http://thesmilingdba.blogspot.com/
Post #906908
magarity kerns
Posted Tuesday, April 20, 2010 8:59 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 10:22 PM
Points: 358, Visits: 393
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!
Post #906929
Trey Staker
Posted Tuesday, April 20, 2010 9:04 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 4:52 PM
Points: 1,379, Visits: 2,626
Wayne, Great article! It came a long ways. :)

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
Post #906941
WayneS
Posted Tuesday, April 20, 2010 9:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:12 AM
Points: 6,370, Visits: 8,235
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
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
Post #906996
UMG Developer
Posted Tuesday, April 20, 2010 10:18 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 13, 2013 11:21 AM
Points: 2,163, Visits: 2,148
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.
Post #907036
matt stockham
Posted Tuesday, April 20, 2010 1:08 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 10:27 AM
Points: 750, Visits: 2,938
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.
Post #907185
WayneS
Posted Tuesday, April 20, 2010 1:26 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:12 AM
Points: 6,370, Visits: 8,235
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
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
Post #907196
UMG Developer
Posted Tuesday, April 20, 2010 1:43 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 13, 2013 11:21 AM
Points: 2,163, Visits: 2,148
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.
Post #907207
SW_Lindsay
Posted Tuesday, April 20, 2010 1:47 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 1:40 PM
Points: 111, Visits: 280
Thanks to all this is why I subscibe to learn something and I learned a lot from this article and subsequent discussion.



steve
Post #907209
« Prev Topic | Next Topic »

Add to briefcase
31 posts, Page 2 of 4
««1234»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.