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?
One Solution collect form web for “Grouping by or iterating through partitions in SQL”
You could use
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.