Limit select rows and skip first / top rows in Oracle and PostgreSQL

Oracle

1. To return a limited number of rows in Oracle, you can use the rownum < number of rows . For example,

     SELECT NAME, PRICE FROM FRUIT WHERE PLACE  = 'FLORIDA' AND rownum < 51 ORDER BY NAME;

     This will allow maximum of 50 rows to be returned.

2. To skip the top number of rows in Oracle.

      SELECT NAME, PRICE FROM FRUIT WHERE PLACE  = 'FLORIDA' 

      MINUS 

      SELECT NAME, PRICE FROM FRUIT WHERE PLACE  = 'FLORIDA' WHERE rownum < 5;

      This will skip the top 4 rows.

     To retrieve rows between row 5 and 9

      SELECT NAME, PRICE FROM FRUIT WHERE PLACE  = 'FLORIDA' WHERE rownum < 10

      MINUS 

      SELECT NAME, PRICE FROM FRUIT WHERE PLACE  = 'FLORIDA' WHERE rownum < 5;

PostgreSQL

1. To return a limited number of rows in PostgreSQL, you can use LIMIT number of rows. For example, the following query returns maximum of 50 rows.

      SELECT  NAME, PRICE FROM FRUIT WHERE PLACE  = 'FLORIDA' ORDER BY NAME LIMIT 50;

2. To skip the top number of rows in PostgreSQL

      SELECT  NAME, PRICE FROM FRUIT WHERE PLACE  = 'FLORIDA' ORDER BY NAME  OFFSET 5;

     This will skip the first 5 rows.

     To retrieve rows between row 5 and 9

      SELECT  NAME, PRICE FROM FRUIT WHERE PLACE  = 'FLORIDA' ORDER BY NAME LIMIT 5 OFFSET 4;

---------------------------------------------------------------------------------------------------

                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.