'SQL to count the instance and total instances on sorted data

Is there an efficient way to add the Instance and Instances columns on sorted SQL? Some kind of self join? I quite often have to do something like this and loop through the data, having to allow for row 1 being a special case, backfilling the total instances with a reverse loop, but I suspect there may be a simpler way.

Example result

sql


Solution 1:[1]

Basic analytic functions would appear what you need:

Select field,
  row_number() over(partition by field order by <some ordering column> ) Instance,
  count(*) over(partition by field) Instances
from YourTable;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Stu

Related Questions