Home » SQL & PL/SQL » SQL & PL/SQL » Displaying records in Pages
Displaying records in Pages [message #219615] Thu, 15 February 2007 03:07 Go to next message
laiko
Messages: 10
Registered: February 2007
Junior Member
I have a table whose rows are retrieved in 10s. I mean there is an application which reads the table. This application displays 10 records at a time. Say, the table is EMPLOYEE and has columns EMP_ID, EMP_NAME, DEPT, SALARY. If the user queries the table using the application, the First page should display the first 10 rows - in any order the user prefers. It may be by ID, name , dept or salary. THen if he clicks on Next Page (say Page 2), rows 11 to 20 should be displayed. Page 3 displays 21 to 30.. so on and so forth..

Given the order by column and the Page as bind variables, how do I write the most efficient SQL? I have something like this in mind:

SELECT sub.emp_id, sub.emp_name, sub.dept, sub.salary
  FROM (SELECT *, rownum r 
          FROM EMPLOYEE
         ORDER BY :order_by_column) sub
 WHERE sub.r < (:page * 10) + 1
MINUS
SELECT sub.emp_id, sub.emp_name, sub.dept, sub.salary
  FROM (SELECT *, rownum r 
          FROM EMPLOYEE
         ORDER BY :order_by_column) sub
 WHERE sub.r > (:page * 10) - 10
Re: Displaying records in Pages [message #219628 is a reply to message #219615] Thu, 15 February 2007 03:55 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
search asktom.oracle.com for pagination.
You can find some good topics there.
Re: Displaying records in Pages [message #219805 is a reply to message #219628] Fri, 16 February 2007 03:33 Go to previous messageGo to next message
laiko
Messages: 10
Registered: February 2007
Junior Member
Thanks Frank. ROW_NUMBER would have been the solution. However, I need an Ansi SQL. ROW_NUMBER is specific to Oracle only. Is it possible?
Re: Displaying records in Pages [message #219811 is a reply to message #219805] Fri, 16 February 2007 03:41 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Oh dear.. Database independency.
While you are at AskTom's site, search for that term as well. Might give you some insight in what you can expect in your project.
Previous Topic: Help required in optimizing the query response
Next Topic: guys oracle 10g migration problem..pls help :-(!!
Goto Forum:
  


Current Time: Fri Dec 02 20:54:21 CST 2016

Total time taken to generate the page: 0.31351 seconds