| Query certain number rows [message #371711] |
Tue, 28 November 2000 12:06  |
Jane
Messages: 17
Registered: November 2000
|
Junior Member |
|
|
I have a table with following fields: id, name, transaction, transaction date, ect... Now I need a select statement which will get the last 10 transactions from the table. Is there a way to do it?
Appreciate your help
|
|
|
|
|
|
| Re: Query certain number rows [message #371719 is a reply to message #371713] |
Wed, 29 November 2000 10:46  |
Jane
Messages: 17
Registered: November 2000
|
Junior Member |
|
|
I tried, but it takes forever to execut the query (It is already half hour past, and I haven't seen the results yet. I have 110056 rows in the table, I know it is very big). Can I somehow use the RowNum to do the query?
Thanks
|
|
|
|
| Re: Query certain number rows [message #371721 is a reply to message #371713] |
Wed, 29 November 2000 11:27 |
Bala
Messages: 205
Registered: November 1999
|
Senior Member |
|
|
Hi,
If you have oracle 8.1.0 and above
then
select transactions from
(select transactions from t1 order by transaction_date desc)
where rownum <=10;
should give you the results.
|
|
|
|
|
|
|
|
|
|
| Re: Query certain number rows [message #371726 is a reply to message #371713] |
Wed, 29 November 2000 15:11 |
Bala
Messages: 205
Registered: November 1999
|
Senior Member |
|
|
Hi,
Only oracle above 8.1.0 you can use order by in a subselect or a view.
Try the first query i gave to you,
create index on transaction_date, it might speed up the query a bit.
or else
a piece of plsql code can do that
create a stored proc by executing this code....
create or replace procedure test_proc1
is
vid number;
vtransaction_date date;
cursor c1 is
select * from t1
order by transaction_date desc;
trnx_rec c1%rowtype;
begin
for trnx_rec in c1 loop
exit when c1%rowcount > 10;
vid := trnx_rec.id;
vtransaction_date := trnx_rec.transaction_date;
dbms_output.put_line(vid || vtransaction_date);
end loop;
end;
/
after that, from your sqlplus promt
SQL> set serveroutput on size 10000;
SQL> exec test_proc1
............
change the table_name, column_names in the procedure according to what you have........
Good luck
Bala
|
|
|
|
| Re: Query certain number rows [message #371738 is a reply to message #371713] |
Thu, 30 November 2000 22:21 |
J.S HAN
Messages: 12
Registered: November 2000
|
Junior Member |
|
|
Hi,
if you really want do that in large database,
you should following.
First. Create index on tansaction date.
second.
select /*+ index_desc(tab tab_index) */ *
from tab
where rownum <= 10;
|
|
|
|
| Re: Query certain number rows [message #371739 is a reply to message #371713] |
Thu, 30 November 2000 22:23 |
J.S HAN
Messages: 12
Registered: November 2000
|
Junior Member |
|
|
Hi,
if you really want do that in large database,
you should following.
First. Create index on tansaction date.
second.
select /*+ index_desc(tab tab_index) */ *
from tab
where rownum <= 10;
|
|
|
|
|
|
| Re: Query certain number rows [message #371748 is a reply to message #371713] |
Fri, 01 December 2000 11:36 |
Jane
Messages: 17
Registered: November 2000
|
Junior Member |
|
|
Hi,
I tried and it worked nice and fast. But I don't understand what /*+ and */* mean, could you explain them to me? And if I want only centain fields (not all the fields) to be displayed, how should I change the statement?
|
|
|
|
| Re: Query certain number rows [message #371757 is a reply to message #371713] |
Fri, 01 December 2000 22:50 |
J.S HAN
Messages: 12
Registered: November 2000
|
Junior Member |
|
|
Hi. Jane
if you want only certain fileds.
SELECT /*+ index_desc(a index_name) */
col1, col2,....
FROM table a
WHERE .....
* --> column name.
/*+ .... */ means hints
hints drived access paths.
index_desc means access data by index and reverse.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
