select random rows in mysql

Question

I have one table named Mydata as follows

     id        name          type
--------------------------------------------
     1         vinu            1
     2         rinu            2
     3         dilu            1
     4         raju            2
     5         manu            3
     6         saju            3
     7         ragu            3
     8         sonu            1
     9         sam             1
     10        rag             1
--------------------------------------------

I want to print records with alternating type, for example:

First row with type =1
Second row with type =2
Third row with type =3
4th row type=1
5th row type=2 and so on

Required result as follows

     id            name            type
   -----------------------------------------
     1              vinu            1
     2              rinu            2
     5              manu            3
     3              dilu            1
     4              raju            2
     6              saju            3
     8              sonu            1
     7              ragu            3
     9              sam             1
     10             rag             1
 ----------------------------------------------

What did you tried? You need to put the code that you tried. — Romnick Susa
– Romnick Susa, Commented Oct 12, 2015 at 7:00
I don't think he wants that query. He needs an order for the given type. — Romnick Susa
– Romnick Susa, Commented Oct 12, 2015 at 7:02
Tarun, what relation do you see between the question and your answer? :) — Federico Razzoli
– Federico Razzoli, Commented Oct 12, 2015 at 7:04
This is the critical question, and makes an answer so difficult because the relation is not too clear. — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 12, 2015 at 7:05

fancyPants · Accepted Answer · 2015-10-12 07:28:54Z

2

Sample data:

CREATE TABLE t
    (`id` int, `name` varchar(4), `type` int)
;

INSERT INTO t
    (`id`, `name`, `type`)
VALUES
    (1, 'vinu', 1),
    (2, 'rinu', 2),
    (3, 'dilu', 1),
    (4, 'raju', 2),
    (5, 'manu', 3),
    (6, 'saju', 3),
    (7, 'ragu', 3),
    (8, 'sonu', 1),
    (9, 'sam', 1),
    (10, 'rag', 1)
;

Query:

SELECT id, name, type FROM (

    SELECT
    t.*,
    @rn := IF(@prev_type = type, @rn + 1, 1) AS rownumber,
    @prev_type := type
    FROM
    t
    , (SELECT @rn := 0, @prev_type := NULL) var_init_subquery
    ORDER BY type

) sq
ORDER BY rownumber, type

Result:

| id | name | type |
|----|------|------|
|  1 | vinu |    1 |
|  4 | raju |    2 |
|  5 | manu |    3 |
|  9 |  sam |    1 |
|  2 | rinu |    2 |
|  7 | ragu |    3 |
|  8 | sonu |    1 |
|  6 | saju |    3 |
| 10 |  rag |    1 |
|  3 | dilu |    1 |

see it working live in an sqlfiddle

Caveat:

Don't expect this to be performant when you have lots of data. It's doing a full table scan.

Here's a manual entry to read when you're interested about how this variables work.

answered Oct 12, 2015 at 7:28

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jerko W. Tisler Over a year ago

very nice answer fancyPants

Tim Biegeleisen · Accepted Answer · 2015-10-12 07:11:35Z

0

This cannot be done via a raw SQL query. Extract the rows you need to display, and then sort them via your application.

Alternatively... you could write a stored procedure, but I don't recommend this. You will need a temporary table and a cursor (that transparently creates another temporary table). Too much for a query that should be executed often.

edited Oct 12, 2015 at 7:11

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

answered Oct 12, 2015 at 7:08

Federico Razzoli

5,5411 gold badge22 silver badges22 bronze badges

1 Comment

fancyPants Over a year ago

Never say never. I have a working way in my answer. I agree though, that a stored procedure with a cursor would be overkill.

Collectives™ on Stack Overflow

select random rows in mysql

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related