help? [message #373193] |
Tue, 03 April 2001 13:27 |
nigel
Messages: 7 Registered: April 2001
|
Junior Member |
|
|
hello can anyone help,
how do you select a certain number of rows to view, say to view 5 rows? how would you do that?
how you do view the top 10 rows in order of date, as i can only get them with the date earliest dates first - i need to list the 10 dates with the most recent date first in list.
can anyone help? thanks!
|
|
|
Re: help? [message #373194 is a reply to message #373193] |
Tue, 03 April 2001 14:36 |
Madhav Kasojjala
Messages: 42 Registered: November 2000
|
Member |
|
|
hi,
you have 2 questions.
First one: How to see certain no of rows only
from table?
Ans:
Select col1,col2,....
from table_name
where rownum between 1 and 4;
here you can change 1, 4 as parameters like
Select col1,col2,....
from table_name
where rownum between &from_row and &to_row;
System will prompt for from_row, to_row values.
Question 2: How to get latest dates first?
To do this, modify the select statement as
select ...
from ....
where....
order by your_date_column_wanted_to_be_sorted desc;
HTH;
Ex: If your table Parts has Partno, Partdesc, Create_Date then
Final statement will b
select Partno, Partdesc, Create_Date
from Parts
where rownum between &from_row and &to_row
order by create_date desc
|
|
|
Re: help? [message #373195 is a reply to message #373193] |
Tue, 03 April 2001 14:42 |
ash
Messages: 43 Registered: February 2001
|
Member |
|
|
I think the best solution can be .. to use inline query.
First of all write the select statement using order by..
and send select rows from that inline query with rownum < 10 .
For example
select x.my_date
from (select date1 from tab1 order by date1 desc) x
where rownum < 10
|
|
|
Re: help? [message #373196 is a reply to message #373193] |
Tue, 03 April 2001 14:42 |
ash
Messages: 43 Registered: February 2001
|
Member |
|
|
I think the best solution can be .. to use inline query.
First of all write the select statement using order by..
and send select rows from that inline query with rownum < 10 .
For example
select x.my_date
from (select date1 from tab1 order by date1 desc) x
where rownum < 10
|
|
|
|
Re: help? [message #373203 is a reply to message #373193] |
Wed, 04 April 2001 02:53 |
Joachim Lindner
Messages: 30 Registered: February 2001
|
Member |
|
|
1. If there are no requirements in terms of specific selection criteria use ...
select * from tab where rownum < NoOfRowsRequested+1
Be careful with any "... where rownum between ... and ..." suggestions. They work only
if the lower boundary is 1. If the lower boundary is > 1 they return NOTHING at all.
2. If there are such requirements as outlined in your example use ...
select * from tab a where NoOfRowsRequested >
( select count(*) from tab b where b.DateColumn > a.DateColumn )
order by a.DateColumn desc;
This approach is common practice and has been posted several times in this
board and other boards, too. It was not me, who invented it ...
Cheers, Joachim
|
|
|
Thanks everyone [message #373223 is a reply to message #373194] |
Wed, 04 April 2001 17:42 |
nigel
Messages: 7 Registered: April 2001
|
Junior Member |
|
|
Thanks for your help people, hopefully when i am great at sql i will be able to help you!!!! you never know it might happen!!!! yeah right!!!
thanks again
|
|
|