rank function always brings back contiguous ranking

I have a simple table that has ids in them.

1
5
22

When I do the following

  • FK referencing PK with same column
  • adding a column to a SQL table in VB using ADO.NET commands
  • SELECT command: String or binary data would be truncated
  • Create job in sql server agent using view
  • Triggers vs Service Broker vs dedicarted Windows Service for high throughput asynchronous record processing via stored procedure
  • What effect does HOLDLOCK have on UPDLOCK?
  • select *, rank() over (order by id) as rank from my table
    

    the rank comes back as

    1
    2
    3
    

    I don’t understand why the ranking is contiguous? I was expecting something like

    1
    4
    17
    

    I would have expected the contiguous behavior from the dense_rank function.

    3 Solutions collect form web for “rank function always brings back contiguous ranking”

    All ID’s are different, that is the reason for that behaviour. Ranking functions come into play when the values you order by are equal.

    create table TableName(id int);
    insert into TableName values(1);
    insert into TableName values(5);
    insert into TableName values(5);
    insert into TableName values(22);
    insert into TableName values(22);
    insert into TableName values(22);
    
    select *, 
    rank() over (order by id) as rank,
    dense_rank() over (order by id) as dense_rank,
    row_number() over (order by id) as row_num
    from TableName
    
    ID  RANK    DENSE_RANK  ROW_NUM
    1   1         1         1
    5   2         2         2
    5   2         2         3
    22  4         3         4
    22  4         3         5
    22  4         3         6
    

    Demo

    Ranking Functions (Transact-SQL)

    Are the id’s contiguous? If there are no ties, then RANK and DENSE_RANK will return the same values.

    Try this instead:

    ;WITH CTE
    AS
    (
      SELECT *, 
        RANK() OVER(ORDER BY id) as rank
      FROM tablename
     )
    SELECT 
      c1.Id,
      (c1.id - ISNULL(c2.ID, 0)) rank
    FROM CTE c1
    LEFT JOIN cte c2 ON c1.rank - c2.rank = 1;
    

    SQL Fiddle Demo

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.