SQL Server 2005's Ranking Functions

You can create columns that provide unique ranking/sorting capabilities. For example, ROW_NUMBER() (produces unique row numbers):

SELECT ROW_NUMBER() OVER (ORDER BY name) AS ROWNUM, name FROM sysobjects; The preceding query produces the following results:ROWNUM name -------------------- -------------------------------------------------------------------------------------------------------------------------------- 1 CoOp 2 Country 3 DF__dtpropert__versi__77BFCB91 4 DF_Carriage_CarriageTypeName 5 DF_CoOp_CoOpName 6 DF_Country_CountryName 7 DF_ExceptionLog_Occurred 8 DF_History_ActionDateTime 9 DF_History_ActionTakenByUserID 10 DF_History_Comments 11 DF_History_ETA 12 DF_History_LastNoticeStatusID ..... Or, there's RANK() (produces non-unique sorting numbers with possible gaps/skips in the sequence)...SELECT RANK() OVER (ORDER BY name asc) AS RANK, name FROM master..syscolumns WHERE LEN(name) > 0; That query produces this:RANK name -------------------- -------------------------------------------------------------------------------------------------------------------------------- 1 @10data 1 @10data 3 @11data 3 @11data 5 @12data 5 @12data 7 @13data 7 @13data 9 @14data 9 @14data 11 @15data 11 @15data 13 @16data 13 @16data ..... Or, there's DENSE_RANK() (produces non-unique sorting numbers with NO gaps/skips in the sequence)...SELECT DENSE_RANK() OVER (ORDER BY name asc) AS RANK, name FROM master..syscolumns WHERE LEN(name) > 0; That will produce this:RANK name -------------------- -------------------------------------------------------------------------------------------------------------------------------- 1 @10data 1 @10data 2 @11data 2 @11data 3 @12data 3 @12data 4 @13data 4 @13data 5 @14data 5 @14data 6 @15data 6 @15data 7 @16data 7 @16data ...... Then there's the interesting NTILE(n) function. This will produce partitioned/divided result sets with equal sequenced numbers, like this:WITH SystemObjects AS ( SELECT TOP 10 name FROM master..sysobjects ) SELECT NTILE(5) OVER (ORDER BY name) AS 'NTILE(5)', name from SystemObjects; That query produces the following output, with the numbers 1 through 5 having a set of 2 for each divided/partitioned section:NTILE(5) name -------------------- -------------------------------------------------------------------------------------------------------------------------------- 1 all_columns 1 all_objects 2 all_parameters 2 all_sql_modules 3 all_views 3 allocation_units 4 assemblies 4 assembly_files 5 assembly_modules 5 assembly_references