General information
Title:
Select records N to N + 10 with order by clause
Hi,
We have a web application backending onto an Oracle 9i database.
We have a requirement to do a query against the tables and display browse screens from which the user can select the record he wishes to view/edit. The screen displays ten records at a time, with 4 or 5 fields from the table displayed in a grid. The user can order by any of those 4 or 5 fields. In addition, the user can select from hyperlinks at the bottom of the screen to see the first 10 records, records 11 to 20 etc.
The following query works, but doesn't order the data:
(SELECT id_accident_case_data FROM (SELECT accident_case_data.id_accident_case_data,accident_case_data.form2no,accident_case_data.inj_name,accident_case_data.hkid,accident_case_data.emp_name FROM accident_case_data ORDER BY accident_case_data.inj_name desc) where rownum <= 10) MINUS (SELECT id_accident_case_data FROM (SELECT accident_case_data.id_accident_case_data,accident_case_data.form2no,accident_case_data.inj_name,accident_case_data.hkid,accident_case_data.emp_name FROM accident_case_data ORDER BY accident_case_data.inj_name desc) where rownum < 1);
Any attempt we have used to order the result, for instance by doing the following:
(SELECT id_accident_case_data FROM (SELECT accident_case_data.id_accident_case_data,accident_case_data.form2no,accident_case_data.inj_name,accident_case_data.hkid,accident_case_data.emp_name FROM accident_case_data ORDER BY accident_case_data.inj_name desc) where rownum <= 10 ORDER BY inj_name desc) MINUS (SELECT id_accident_case_data FROM (SELECT accident_case_data.id_accident_case_data,accident_case_data.form2no,accident_case_data.inj_name,accident_case_data.hkid,accident_case_data.emp_name FROM accident_case_data ORDER BY accident_case_data.inj_name desc) where rownum < 1 ORDER BY inj_name desc);
results in the error "Missing right parenthesis".
Anyone got any idea how to proceed ?
Best.
Matt.
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only