Grouping by or iterating through partitions in SQL

Two part question regarding partitioning in SQL.

In T-SQL when you use PARTITION BY is there a way to assign a unique number to each partition, in addition to something like row_number()?

  • How can I change my default database in SQL Server without using MS SQL Server Management Studio?
  • Crystal Reports not showing stored procedure outputs
  • How to change identity column values programmatically?
  • Using cross apply in update statement
  • Error while enabling CDC on table level
  • E.g. row_number() would yield,

    Action          Timestamp           RowNum
    A               '2013-1-10'         1
    A               '2013-1-11'         2
    B               '2013-1-12'         1
    B               '2013-1-13'         2

    Whereas, in addition, uniquely identifying each partition could yield,

    Action          Timestamp           RowNum          PartitionNum
    A               '2013-1-10'         1               1
    A               '2013-1-11'         2               1
    B               '2013-1-12'         1               2
    B               '2013-1-13'         2               2

    Then one could GROUP BY partition number.

    Second part of my question is, how can you break out each partition and iterate through it, e.g.,

    for each partition p
       for each row r in p 
           do F(r) 

    Any way in T-SQL?

  • Partition Or Separate Table
  • Partitioning Views in sql
  • Full Text Search Auto-Partition Schemes and Functions
  • Can I ALTER a partitioned table in SQL Server 2005?
  • Partition Exchange as publishing technique in SQL Server?
  • Will SQL Server Partitioning increase performance without changing filegroups
  • One Solution collect form web for “Grouping by or iterating through partitions in SQL”

    You could use dense_rank():

    select  *
    ,       row_number() over (partition by Action order by Timestamp) as RowNum
    ,       dense_rank() over (order by Action) as PartitionNum
    from    YourTable

    Example at SQL Fiddle.

    T-SQL is not good at iterating, but if you really have to, check out cursors.

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