Paging (oracle database)

Question

I need to create request to oracle database from my C# code what will select rows for pages at web site. I want for example for get rows from 100 to 200 for second page. I found some code what real works

SELECT * FROM
(
   SELECT a.*, rownum r__
   FROM
   (
      SELECT * FROM ORDERS WHERE CustomerID LIKE 'A%'
      ORDER BY OrderDate DESC, ShippingDate DESC
   ) a
   WHERE rownum < ((pageNumber * pageSize) + 1 )
)
WHERE r__ >= (((pageNumber-1) * pageSize) + 1)

It works, I can use it when change this block inside wrap

SELECT * FROM ORDERS WHERE CustomerID LIKE 'A%'
ORDER BY OrderDate DESC, ShippingDate DESC

But how does it work? I can't understand. I'm familiar with SQL requests but what is

SELECT a.*, rownum r__  

and what is

 ) a

after FROM wrap?

Can you be so kind to explain me this wrap?

Answer 1

select a.*, rownum ->Select all records from table/table alias a, along with rownumber, so you can select a subset.

(SELECT * FROM ORDERS WHERE CustomerID LIKE 'A%' ORDER BY OrderDate DESC, ShippingDate DESC) a

Creates 'a' as a table alias allowing you to treat it as a table in outer select table.

As a note, select * is considered bad practice as changes to the table structure will likely break your code.