0

I have one table with name emp with columns id, name, address. I want SELECT records first 10 and then next 10 and so on depends on the variable. Please let me know how can I achieve this?

MT0
  • 143,790
  • 11
  • 59
  • 117
user15365547
  • 27
  • 5

1 Answers1

-3

You can use below SELECT statement:

SELECT * FROM EMP where rownum between 1 and 10;

You can use 1 and 10 as two variables and replace it dynamically.

Sachin Padha
  • 211
  • 1
  • 4
  • 1
    Please provide also an answer for "then next 10 and so on" – astentx Jul 27 '22 at 11:41
  • Great ! it worked. I have implemented it like this: SELECT * FROM EMP where rownum between ? AND ? ... And while executing the query I have passed these variables. – user15365547 Jul 27 '22 at 11:42
  • 1
    @user15365547 This wouldn't work for "next", because rownum is a pseudo-column that enumerates rows as they appear *in the output*. Any filter where rownum > 1 will return empty result set. See [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_21&fiddle=229c9b0fd62e7cb6ba00926e49ef67c6) – astentx Jul 27 '22 at 11:44
  • But in my case it is working. I don't why you are thinking otherwise. I can't copy paste entire code here due to certain policies. I am closing and accepting the answer. Thank you very much ! – user15365547 Jul 27 '22 at 11:47
  • 1
    @user15365547 This will get the first 10 (effectively random) rows and return them. It does not order the rows and if you include an `ORDER BY` clause then it will still get 10 random rows and then order those random rows; it does **NOT** order the rows and then get the first 10 (you would need to use nested sub-queries for that). – MT0 Jul 27 '22 at 11:48
  • I request you humbly. That code worked for. On what basis you down it -ve vote? If you are doing these kinds of voting, I am afraid you will give lot of wrong impression. In my case it worked, so always be polite and don't become rude by voting it down. It is community and please don't discourage others. – user15365547 Jul 27 '22 at 11:52
  • 1
    @user15365547 The answer is wrong. It will not work for rows 11 to 20 and it will not get the correct rows if you use `ORDER BY` [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=8c358bee8fd65efe51b652d70693976c) – MT0 Jul 27 '22 at 11:58
  • 1
    @user15365547 Either the question is incorrect or you didn't test it against your requirement. You clearly ask: "I want SELECT records first 10 **and then next 10 and so on** depends on the variable". This answer doesn't provide you a solution for "so on" and it is misleading for future readers – astentx Jul 27 '22 at 12:03