Sometimes I stumbled upon requirement where I have to create a table with paging, where the data will be populated from database. Usually, to get total rows and the actual data, I will run two query:
1. select * from tbl_test where rownum between :top and :btm
2. select count(*) from tbl_test
Today, I found a really similar case and a very excellent solution at Stack Overflow which allows me to fetch data and count total rows in a single query with great performance. Here is the query I end up with:
select * from
(select a.*,rownum as rnum from
(select test_id,test_desc,count(*) over () total_rows from tbl_test order by test_id) a)
This count(*) over () solution works really well on Oracle. When I have the time, I’ll try it on another RDBMS to find out whether this can work in another DB or not. But for now, I’m happy haha.