Home » SQL & PL/SQL » SQL & PL/SQL » How to find n max rows from a table without using "order by" or "group by"
How to find n max rows from a table without using "order by" or "group by" [message #37665] Tue, 19 February 2002 18:42 Go to next message
mani
Messages: 105
Registered: September 1999
Senior Member
Hi,

I want to find n max rows from a table which consists of large amount of data's such as 10 lakhs rows.

Consider the following sample table.

id name value

19 A 22
18 B 22
1 C 33
55 D 1
45 E 11
7 F 10
88 G 2
22 H 5
33 I 7
2 J 6
9 K 8
44 L 4


Like this nearly 10 laksh rows will be there.I want to get first 4 max(id) rows or last 4 max(id) rows or 5th to 9th max(id) rows.

I tried with some of the queries using "order by" and "group by".But it consumes some time for me which effects our performance very badly.Even I indexed all the columns in the table, no such performance improving due to "order by" and "group by" in the queries.

Could anyone provide me a solution for this....

Thanks a lot.
Re: How to find n max rows from a table without using "order by" or "group by" [message #37674 is a reply to message #37665] Wed, 20 February 2002 00:15 Go to previous messageGo to next message
tinel
Messages: 42
Registered: November 2001
Member
Hi

Here is a code that may help you:

create or replace procedure proc_extr_n_rows
(n in pls_integer, position in number)
as
cursor c1 is select * from emp;
c1_rec c1%rowtype;
int_i pls_integer := 1;
begin
open c1;
loop
fetch c1 into c1_rec;
exit when c1%notfound;
if ((int_i >= position) and (int_i <= position+n-1)) then
dbms_output.put_line(c1_rec.ename);
end if;
int_i:= int_i + 1;
exit when int_i > position+n;
end loop;
end;

Bye
Re: How to find n max rows from a table without using "order by" or "group by" [message #37698 is a reply to message #37665] Thu, 21 February 2002 18:24 Go to previous messageGo to next message
mani
Messages: 105
Registered: September 1999
Senior Member
Hi ,

Thanks a lot for your kind reply. Still i need
some more help.

I am rephrasing my problem again here.
Consider a sample table.

ID NAME VALUE

22 A 4
86 B 5
45 C 0
34 E 8
12 F 7
10 G 4
2 Y 99
5 T 33
99 R 33

My query may be like the following :

1. select first MAX(ID) 4 rows from the table (or
select 4 rows from table order by ID DESC)

for this result should be like this.

ID NAME VALUE

99 R 33
86 B 5
45 C 0
34 E 8

2. select last MAX(ID) (or MIN(ID)) 4 rows from the
table or (select 4 rows from table order by ID ASC)

for this result should be like this.

ID NAME VALUE

2 Y 99
5 T 33
10 G 4
12 F 7

3. select 4th row to 5th row of sample table for the
condition MAX(ID) (or select 4th to 5th row from
sample order by ID ASC)

for this result should be ..

ID NAME VALUE

12 F 7
22 A 4

In my database nearly one million entries are
there.
I tried with some queries using "order by" clause.But
it consumes some time to give me the resultset.And
this effects our performance badly.So we need a better
query without using "order by" or "group by" or "NOT
IN" clause.

Eventhough i indexed ID column, but no performace
for "order by" and "NOT IN" clause.

Coule you please solve my problem.

Once again Thanks a lot for your reply.....
Re: How to find n max rows from a table without using "order by" or "group by" [message #37707 is a reply to message #37665] Fri, 22 February 2002 04:59 Go to previous messageGo to next message
tinel
Messages: 42
Registered: November 2001
Member
Hi
Here is a procedure that i think may help you.
CREATE OR REPLACE procedure proc_mani
(condition in varchar2, number_of_rows in number, first_index in number, last_index in number)
as
/*
parameter condtition specify the rows to be returned, max, min, between
parameter number_of_rows specify the number of rows to be returned.
*/
TYPE Ref_Cursor is ref cursor;
c1 ref_cursor;
var_id number(3);
var_name varchar2(10);
var_value number(3);
num_i pls_integer;
begin
if (upper(condition) = 'MAX') then
open c1 for 'select id, name, value from tbl_mani a where ' || number_of_rows || ' > ' ||
'(select count(*) from tbl_mani where id > a.id)';
loop
fetch c1 into var_id, var_name, var_value;
exit when c1%notfound;
dbms_output.put_line(to_char(var_id) || ' ' || var_name || ' ' || to_char(var_value));
end loop;
close c1;
elsif (upper(condition) = 'MIN') then
open c1 for 'select id, name, value from tbl_mani a where ' || number_of_rows || ' > ' ||
'(select count(*) from tbl_mani where id < a.id)';
loop
fetch c1 into var_id, var_name, var_value;
exit when c1%notfound;
dbms_output.put_line(to_char(var_id) || ' ' || var_name || ' ' || to_char(var_value));
end loop;
close c1;
elsif (upper(condition) = 'BETWEEN') then
for i in (first_index-1)..(last_index-1) loop
open c1 for 'select id, name, value from tbl_mani a where ' || i || ' = ' ||
'(select count(*) from tbl_mani where id < a.id)';
loop
fetch c1 into var_id, var_name, var_value;
exit when c1%notfound;
dbms_output.put_line(to_char(var_id) || ' ' || var_name || ' ' || to_char(var_value));
end loop;
close c1;
end loop;
else
dbms_output.put_line('Not a valid condition, please use MAX, max, MIN, min, BETWEEN, between');
end if;
end;
/

Bye
Re: How to find n max rows from a table without using "order by" or "group by" [message #37760 is a reply to message #37665] Wed, 27 February 2002 00:51 Go to previous message
mani
Messages: 105
Registered: September 1999
Senior Member
Hi ,

Thanks a lot for your kind reply.I tested your storage procedure with 50,000 records in the table.
At the time , CPU Usage has gone to 90% to 95% and it consumes lot of time.Then i tested that particular query alone with 50,000 records, it also consumes lot of time.For records less than 1000 it seems to be okay.
The query that i made to test was:

select id from mani_table a where 5 > (select count(*) from mani_table where id > a.id);

I need some other logic to get the output within minimal time for 10 million records.

Thanks
mani
Previous Topic: urgent reg Index
Next Topic: Re: Cummulative SUM
Goto Forum:
  


Current Time: Thu Mar 28 05:10:17 CDT 2024