0

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

1 Answer 1

3

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 |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.