Oracle Select Row And Count

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

However, running two queries has its toll. I ended executing the same thing twice. Indeed, I can use a Javascript library to fetch the limited data and count total rows, but I prefer to do this at database level because it’s simpler for me.

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.

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s