CODEWITHSUNDEEP
sql-servert-sqlpartitioning
Sean Thoman
Sean Thoman

Reputation: 7499

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()?

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?

Upvotes: 1

Views: 2803

Answers (1)

Andomar
Andomar

Reputation: 238296

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.

Upvotes: 3

Related Questions