Oracle Getting rowcount in same query which is used to fetch rows

This is small thing but handy when you need to get the total rows being fetched and actual data from the single query.
This might not be issue if the user retrieves all the records  by given query because user can simply iterate over the resultset and count the number of records being fetched. But this wont help you when you display only specific number of records in your query(ex: where rownum between 10 and 20).
Now this solution comes into role. Simply use following portion of code in your sql query that is used to fetch the record:

            ROW_NUMBER() OVER (ORDER BY [any_field] desc) row_num,
            Count(*) OVER() cnt

where any_field is the fieldname that is being fetched by the sql.

Your complete query should be something like this:

SELECT FIELD1,FIELD2..........FIELDN,
            ROW_NUMBER() OVER (ORDER BY fileid desc) row_num,
            Count(*) OVER() cnt
FROM MYTABLE
WHERE ROWNUM>=1 AND ROWNUM<=100.

Now the resultset returned has the column CNT that holds the total records satisfied by the above sql with where condition being excluded.

This type of query becomes handy when you need to implement pagination in your application.
The above query is a part of working sql in Oracle 11g.

Thanks to the url http://stackoverflow.com/questions/2905199/oracle-how-get-total-number-of-results-when-using-a-pagination-query which gives some idea on this..