Incremental column depening on column value I would like to have a column which has an incremental integer depending on the column value after sorting the table.

E.g. after sorting by state:

Nr  | State      | Counter
---------------------------
234 | Alberta    | 1
534 | Alberta    | 2
111 | Alberta    | 3
123 | California | 1
876 | New York   | 1
101 | New York   | 2
542 | New York   | 3
533 | New York   | 4


Is this even possible with MySQL?
Just found the solution after banging my head into the wall:

SELECT
  t.*,
  @row := IF(@lastState = t.State, @row + 1, 0) as counter,
  @lastFunkt := t.State
FROM
  table t,
  (SELECT @row := 0,
          @lastState := '') SQLVars