Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

SQL Server Ranking Functions Expand / Collapse
Author
Message
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
Posted Tuesday, April 20, 2010 8:44 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 10:01 AM
Points: 2,980, Visits: 762
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
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: Wednesday, January 15, 2014 10:21 AM
Points: 358, Visits: 396
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
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: Monday, March 10, 2014 3:52 PM
Points: 1,380, Visits: 2,681
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
Posted Tuesday, April 20, 2010 9:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:59 AM
Points: 6,544, Visits: 8,761
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
Posted Tuesday, April 20, 2010 10:18 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, March 27, 2014 8:50 AM
Points: 2,163, Visits: 2,184
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
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: Today @ 12:25 AM
Points: 750, Visits: 3,085
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
Posted Tuesday, April 20, 2010 1:26 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:59 AM
Points: 6,544, Visits: 8,761
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
Posted Tuesday, April 20, 2010 1:43 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, March 27, 2014 8:50 AM
Points: 2,163, Visits: 2,184
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
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: Thursday, December 19, 2013 6:49 AM
Points: 120, Visits: 291
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 ««1234»»»

Permissions Expand / Collapse