0
SELECT id FROM (
    SELECT 
    id, -- primary key
    rownum rownum_
    from test
    where time BETWEEN TO_DATE('2021-01-01 00:00:00', 'yyyy-MM-dd hh24:mi:ss') AND TO_DATE('2021-10-25 23:59:59', 'yyyy-MM-dd hh24:mi:ss')
    and ROWNUM <= 15000
    ORDER BY id
) WHERE rownum_ > 14000

Query according to the time range, in paging,But there will be duplicate data. the data of 14000~15000 will reappear at 18000~19000. There are 9,000 duplicates of 100,000 data.

user9628169
  • 1
  • The `ROWNUM` filter is applied before the `ORDER BY` clause is applied so you will get 15000 random rows which get ordered and then you get the last 1000 of them. As per the linked duplicate, you need to `ORDER BY` first then in an outer query generate the `ROWNUM` (and possibly filter the upper bound) and then in a further outer query filter the lower bound. – MT0 Nov 04 '21 at 11:24

0 Answers0