994638 wrote:rows in a table are like balls in a basket.
I have an Accounts table with say 100K records. I selected the first unique 1000 by simply doing <=1000 and the second 1000 by doing not in(the first 1000). How can I select the third unique 1000 records whereby I dont have any of the first 2000?
DROP TABLE testt; CREATE TABLE TESTT ( STR VARCHAR2 (100 BYTE), TIMES TIMESTAMP (6) ); INSERT INTO testt SELECT DBMS_RANDOM.string (1, 6) str, SYSDATE + ROWNUM * 0.005 / 24 FROM DUAL CONNECT BY ROWNUM <= 5000; COMMIT; SELECT * FROM (SELECT ROW_NUMBER () OVER (PARTITION BY 1 ORDER BY times ASC) r, str, times FROM testt) a WHERE r BETWEEN 2000 AND 3000 AND NOT EXISTS (SELECT 1 FROM testt b WHERE a.str = b.str AND a.r < 2000);
994638 wrote:No, it's wrong. There is no ORDER BY clause so it is picking any 1000 rows (it might be the ones you want, it might not be).
Thanks Manik .... it worked like a charm.