> Subject: > Limiting rows > Date: > Mon, 13 Jul 1998 14:14:10 +0200 > From: > Stefan Bleicher> To: > akaplan@interaccess.com > > Hi Ari, > > I have a question about limiting the rows being returned by a select > statement. > > I have a large table and want to get only some rows out of this table. I > know the rownum clause, but this is not the > solution to my problem, because I want to get the rows (in alphabetic order) > for example from row 3 to row 5, not > from row 1 to row 5. > > Example: > > NAME > > Alber > Albrecht > Ari > Bleicher > Black > Miller > Percy > Robert > Robin > Walter > > SELECT * FROM ... WHERE ROWNUM < 6 ORDER BY NAME => All rows > 6 => > Alber, Albrecht,Ari, > Bleicher, Black > > But I want to get the result Ari, Bleicher, Black (rows 3 to 5). Do you > know the SQL-statement to get this. > > Please answer to > > sbleicher@ibl.de > > Thanks for your help > Stefan > Stefan, As far as I know this is very hard if not impossible in SQL. This is because ROWNUM is a pseudo-column and you can never have " ROWNUM > " in the WHERE clause. There are two ways I can think of to do this: FIRST METHOD: Use PL/SQL: 1) open a cursor. 2) loop through the cursor keeping track of the record number with a variable. 3) output only the data when the variable is between 3 and 5. SECOND METHOD: 1) Create a new table: CREATE table_b AS SELECT ROWNUM AS ROW_NUMBER, column_a, column_b, ... FROM table_a ORDER BY column_a 2) Your table (table_b) has a new column called ROW_NUMBER. You can select your records with: SELECT * FROM table_b WHERE ROW_NUMBER > 2 AND ROW_NUMBER < 6 Best of luck! -Ari Kaplan Independent Oracle DBA Consultant ------------------------------------------------------------------------------------------------------ MIKE CONRAD GIVES THE FOLLOWING ADVICE: Date: Tue, 1 Feb 2000 15:29:08 -0600 From: "Conrad, Mike" To: "'akaplan@interaccess.com'" Subject: Oracle Tip 220 - Limiting rows to the Top X meeting a condition Here is one way to do this using only SQL and not creating another table. The Length function is only here so that we can use the Group By. select * FROM ( select rownum row_num, A.description from (SELECT description, length(description) FROM whatever_table group by description) A ) B where B.row_num > 2 and B.row_num < 6; Thanks, Michael A. Conrad MetaSolv Software, Inc. 5560 Tennyson Parkway Plano, Texas 75024 Voice: (972) 403-8582 Fax: (972) 403-8333 mconrad@MetaSolv.com http://www.metasolv.com/
Back to Ari Kaplan's Home Page Back to Ari Kaplan's Home Page