how to get values in discrete ranges from a DB table , using SQL Queries

Question

Assume a DB table with values

[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30]

Note: this pattern is not necessary (the sorted order and values from 1-30).

so using a query I can get values between 10-20 and so on.

But here I want to know how I can get values from different ranges. i.e:

one random value from range 0-5
one value form range 6-10
one value from range 11-15
one value from range 16-20

... so on.

So the output of the query will looks like:

[2,7,14,16,23,29,...]

or

[1,6,11,18,22,26,...]

Is there any way I can achieve this.

Manisha Patel · Accepted Answer · 2014-09-08 10:23:29Z

2

Try for this

(SELECT id as r1 FROM `t1` WHERE id between 1 and 5 ORDER BY RAND() limit 0,1)

 union 

(SELECT id as r1 FROM `t1` WHERE id between 6 and 10 order by RAND() limit 0,1)

 union 

(SELECT id as r1 FROM `t1` WHERE id between 11 and 15 order by RAND() limit 0,1)

 union 

 (SELECT id as r1 FROM `t1` WHERE id between 16 and 20 order by RAND() limit 0,1)

union 

(SELECT id as r1 FROM `t1` WHERE id between 21 and 25 order by RAND() limit 0,1)

union 

(SELECT id as r1 FROM `t1` WHERE id between 26 and 30 order by RAND() limit 0,1)

This the query of specified rang if you want make it for dynamically range you need make this query dynamically once you have all the range up to N.

edited Sep 8, 2014 at 10:23

answered Sep 8, 2014 at 10:17

Manisha Patel

3542 silver badges12 bronze badges

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

Collectives™ on Stack Overflow

how to get values in discrete ranges from a DB table , using SQL Queries

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related