Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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 |
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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