Rotate table data with out update data

Question

 SELECT * FROM `your_table` LIMIT 0, 10 

->This will display the first 1,2,3,4,5,6,7,8,9,10

 SELECT * FROM `your_table` LIMIT 5, 5 

->This will show records 6, 7, 8, 9, 10

I want to Show data 2,3,4,5,6,7,8,9,10,1 and next day 3,4,5,6,7,8,9,10,1,2 day after next day 4,5,6,7,8,9,10,1,2,3

IS IT POSSIBLE with out updating any data of this table ???

Answer 1

I don't think this might be achieved using a simple Select (I may be wrong). I think you'll need a stored procedure.

Answer 2

You can do this using the UNION syntax:

SELECT * FROM `your_table` LIMIT 5, 5 UNION SELECT * FROM `your_table`

This will first select rows within your limit, and then combine the remainder from the second select. Note that you don't need to set a limit on the second select statement:

The default behavior for UNION is that duplicate rows are removed from the result. The optional DISTINCT keyword has no effect other than the default because it also specifies duplicate-row removal. With the optional ALL keyword, duplicate-row removal does not occur and the result includes all matching rows from all the SELECT statements.

Answer 3

You've tagged this as Oracle, though your SQL syntax would be invalid for Oracle because it doesn't support LIMIT

However, here's a solution that will work in Oracle:

select *
  from ( select rownum as rn,
                user_id
           from admin_user
          order by user_id
       ) X
 where X.rn > :startRows
   and X.rn <= :startRows + :limitRows
 order by case when X.rn <= :baseRef
                    then X.rn + :limitRows
               else
                    X.rn
          end ASC
;

where :startRows and :limitRows are the values for your LIMIT, and :baseRef is a value between 0 and :limitRows-1 that should be incremented/cycled on a daily basis (ie on day 1 it should be 0; on day 2, 1; on day 10, 9; on day 11 you should revert to 0). You could actually use the current date, converted to Julian and take the remainder when divided by :limitRows to automate calculating :baseRef

(substitute your own column and table names as appropriate)