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 ««12

RANK() returns consecutive integers Expand / Collapse
Author
Message
Posted Thursday, January 6, 2011 9:16 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:38 AM
Points: 4,379, Visits: 3,391
cengland0 (1/6/2011)
There are several ranking functions:
. . . One row is still a non empty rowset but that was not explained in the provided explanation.


You are absolutely right. Thank you for pointing out this boundary case.

In my defense, I am still doing better than SQLS BOL which are not mentioning an empty set.

Post #1043838
Posted Thursday, January 6, 2011 10:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
Thanks for the question!
Post #1043890
Posted Thursday, January 6, 2011 11:33 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:46 PM
Points: 21,187, Visits: 14,880
Thanks for the question.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1043922
Posted Thursday, January 6, 2011 12:37 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 11, 2013 3:13 PM
Points: 477, Visits: 3,671
Is it considered cheating that I went to the BOL before answering the question and read "If two or more rows tie for a rank, each tied rows receives the same rank."?

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Post #1043990
Posted Thursday, January 6, 2011 1:54 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:20 PM
Points: 13,252, Visits: 10,133
toddasd (1/6/2011)
Is it considered cheating that I went to the BOL before answering the question and read "If two or more rows tie for a rank, each tied rows receives the same rank."?


I don't consider doing research and learning from it cheating.
I'm not going to memorize every feature, trace flag, syntax, dmv of SQL Server. If I need something, I'll look it up




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1044020
Posted Friday, January 7, 2011 1:26 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 6:24 AM
Points: 2,451, Visits: 2,342
Koen (da-zero) (1/6/2011)
toddasd (1/6/2011)
Is it considered cheating that I went to the BOL before answering the question and read "If two or more rows tie for a rank, each tied rows receives the same rank."?


I don't consider doing research and learning from it cheating.
I'm not going to memorize every feature, trace flag, syntax, dmv of SQL Server. If I need something, I'll look it up

I agree! It's impossible to remember details of every command of every language.
Post #1044210
Posted Friday, January 7, 2011 4:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 6,581, Visits: 8,857
toddasd (1/6/2011)
Is it considered cheating that I went to the BOL before answering the question and read "If two or more rows tie for a rank, each tied rows receives the same rank."?


"Cheating" implies that this is a test - which it isn't. QotD is designed to do just this - get you looking into areas of SQL that you might not have been aware of. If you researched something, and learned from it, then mission accomplished.


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 #1044292
Posted Tuesday, January 18, 2011 6:03 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, November 17, 2013 11:53 AM
Points: 623, Visits: 237
I knew that Rank() skips the number if there is a tie but thought it's still consecutive number in a sense next available int number.
Post #1049261
Posted Friday, March 2, 2012 3:47 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, June 19, 2014 10:32 AM
Points: 483, Visits: 243
The RANK Function is a pretty interesting topic and a great pick. Below is an great example too using the AdventureWorks200R2 database.

URL: http://msdn.microsoft.com/en-us/library/ms189798.aspx

Thank you for the question.
Post #1261073
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse