how to select a list from random id to the last

Question

im new with SQL. i know to how select a list with limit comand. but that way need a value to select. what if i want select a list from random id to the last. exemple:

I want to select a list with id from 4 -> last of row (cuz i dont know whats last id)

What RDBMS are you using SQL Server, MySQL , Oracle etc..... also what have you tried so far ???? — M.Ali, Aug 24 '15 at 14:19
im using mysql. this is my first time im working with database — Nguyễn Văn Thắng, Aug 24 '15 at 14:23
How do you get the random id ? From user input or a random number generator? — Prerak Sola, Aug 24 '15 at 14:27
i mean "the id that i already know" to "the last id what i dont know" (sr my bad eng) — Nguyễn Văn Thắng, Aug 24 '15 at 14:29
You can execute a query to get the last id like this: `select id from table order by id desc limit 1`. This will give you the last id. Use this in next query like this: `select * from table limit 4, [id form previous query]`. Which server side language are you using? — Prerak Sola, Aug 24 '15 at 14:33

Drew · Accepted Answer · 2015-08-24T14:58:59.630

select * from thing1 where id>=4 order by rand();

where thing1 is your table name. How you seed your random number generator (RNG) is up to you.

+----+---------+------------+
| id | conn_id | read_date  |
+----+---------+------------+
| 11 |       3 | 2013-02-21 |
|  5 |       1 | 2012-02-21 |
|  8 |       5 | 2010-12-21 |
| 15 |       7 | 2019-12-21 |
| 14 |       6 | 2019-12-21 |
| 13 |       5 | 2016-02-21 |
|  4 |       2 | 2010-12-21 |
|  7 |       2 | 2014-02-21 |
|  6 |       2 | 2007-12-21 |
| 12 |       4 | 2014-02-21 |
| 16 |       8 | 2010-12-21 |
|  9 |       3 | 2010-12-21 |
| 10 |       4 | 2010-12-21 |
+----+---------+------------+
13 rows in set (0.14 sec)

mysql> select * from thing1 where id>=4 order by rand();
+----+---------+------------+
| id | conn_id | read_date  |
+----+---------+------------+
| 13 |       5 | 2016-02-21 |
|  6 |       2 | 2007-12-21 |
| 10 |       4 | 2010-12-21 |
| 16 |       8 | 2010-12-21 |
| 14 |       6 | 2019-12-21 |
|  5 |       1 | 2012-02-21 |
|  7 |       2 | 2014-02-21 |
| 11 |       3 | 2013-02-21 |
| 12 |       4 | 2014-02-21 |
|  4 |       2 | 2010-12-21 |
|  8 |       5 | 2010-12-21 |
|  9 |       3 | 2010-12-21 |
| 15 |       7 | 2019-12-21 |
+----+---------+------------+
13 rows in set (0.02 sec)

mysql> select * from thing1 where id>=4 order by rand();
+----+---------+------------+
| id | conn_id | read_date  |
+----+---------+------------+
| 10 |       4 | 2010-12-21 |
|  4 |       2 | 2010-12-21 |
|  6 |       2 | 2007-12-21 |
|  7 |       2 | 2014-02-21 |
|  5 |       1 | 2012-02-21 |
|  9 |       3 | 2010-12-21 |
| 12 |       4 | 2014-02-21 |
| 16 |       8 | 2010-12-21 |
|  8 |       5 | 2010-12-21 |
| 15 |       7 | 2019-12-21 |
| 13 |       5 | 2016-02-21 |
| 14 |       6 | 2019-12-21 |
| 11 |       3 | 2013-02-21 |
+----+---------+------------+
13 rows in set (0.05 sec)

Stored Proc

To have starting random position, until the end, random ordering

-- drop procedure getRandomStartToEnd;
delimiter $$
create procedure getRandomStartToEnd()
BEGIN
    declare theCount int;
    declare theStart int;

    select count(*) into theCount from thing1;
    set @theStart:=floor((rand()*@theCount)+1);
    select * from thing1 where id>=@theStart order by rand();
END
$$

call getRandomStartToEnd;   -- call stored proc

If, we don't use `rand()` we will still get the required results and that too in an ordered manner. — Prerak Sola, Aug 24 '15 at 14:40

how to select a list from random id to the last

1 Answers1

Stored Proc