Getting MySQL User Variable in Sequence, While Using ORDER BY RAND() I am running a SELECT statement, which ultimately will be in an INSERT..SELECT statement.

I want a result like this

ID        COUNTER
3           1
7           2
1           3
20          4
12          5

When I run this query, I get everything in sequence:

SELECT 16132,@counter:=@counter+1 as counter 
  FROM table t 
 INNER JOIN (SELECT @counter:=-1) b
 LIMIT 5;


But I want it to be randomized, so I add ORDER BY RAND() at the end of the query, but then I end up with "random" values in the counter column. I think the randomized counter values match to the larger, entire result set, but is there a way I can get a counter column that will go in sequence when I am using ORDER BY RAND() ?
You need to get your random row-set first and then number the rows.

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE tbl
    (`ID` int)
;

INSERT INTO tbl
    (`ID`)
VALUES
    (1),
    (2),
    (3),
    (4),
    (5),
    (6),
    (7),
    (8),
    (9),
   (10),
   (11),
   (12),
   (13),
   (14),
   (15),
   (16),
   (17),
   (18),
   (19),
   (20)
;


Use LIMIT and ORDER BY RAND() together to get your random row-set:    

Query 1:

SELECT * 
  FROM tbl 
 ORDER BY RAND()
 LIMIT 5;


Results:

| ID |
|----|
| 18 |
| 10 |
| 15 |
| 16 |
|  2 |


Wrap that into an outer query to apply the row numbering:

Query 2:

SELECT ID,@counter:=@counter+1 as counter 
FROM (
  SELECT * 
    FROM tbl 
   ORDER BY RAND()
   LIMIT 5
  )t 
INNER JOIN (SELECT @counter:=-1) b;


Results:

| ID | COUNTER |
|----|---------|
| 19 |       0 |
| 10 |       1 |
|  1 |       2 |
|  7 |       3 |
| 17 |       4 |


Running the same query again to show its really random:

Query 3:

SELECT ID,@counter:=@counter+1 as counter 
FROM (
  SELECT * 
    FROM tbl 
   ORDER BY RAND()
   LIMIT 5
  )t 
INNER JOIN (SELECT @counter:=-1) b;


Results:

| ID | COUNTER |
|----|---------|
|  4 |       0 |
|  8 |       1 |
| 18 |       2 |
| 14 |       3 |
| 16 |       4 |