printing only the first 10 lines of a query

[erixire]

Hi,

Is there a way to print only the first 10 lines (or another number) if I know that the query will produce over 1000 lines?

Thanks

 

[uparna]

Hi erixire,
just add an
'and rownum < 11 ; ' without the quotes
in the where clause of your query. Regards,
S. Jayaram Uparna .

If the need arises,you are welcome to mail me at upparna@yahoo.com .

 

[blarson0]

I think you can also do

SELECT TOP 10

or if you prefer

SELECT TOP # PERCENT
where # is between 0 and 100

It works with access, I assume it will work with oracle...

-Brad

 

[spenglerr]

TOP will not work with Oracle in this way.

To perform a top 10-query you will have to use an inline query:

select * from (select ... from ... where ... order by ...)
where rownum < 10;

Important: Ordering to get the values correct.

Performance: Much better, since Oracle by this way will only order 10 values. There will be a window of 10 records running over the table and there will only be checked if the order-by-values will be larger than the lowest of the present ten records. By this way this query gets really fast

 

[sunpatil]

u can also select any number of rows from in between also using the inner query like if want to select only rows from 25 to 35. for that u have to use rownum and rowcount.

 

[Turkbear]

Actually that may not work as expected...

You can choose to display or print some middle group of rows by using the MINUS operator:

Select field1 from table where rownum < 25
minus 
Select field1 from table where rownum < 10

will return rows 10 to 24, however that alone is pretty meaningless since the order in which the rows are returned in this case is random..

You can use :

Select * from (select field1 from table order by field1) where rownum < 25
minus
Select * from (select field1 from table order by field1) where rownum < 10

That will return rows 10 to 24 of the sorted data..

hth,

 

[Guest_imported]

After much searching online i finally came accross this forum/thread, as i am trying to only return a limited no. of rows ie from 1-100,101-200 etc. i tryed ur method Turkbear but and it does work but is horribly inefficent over doubling the time taken to return the rows required! As a programmer i apears to be more efficent to not use minus function and deal with the data in the calling app. while this will use more memory on the client side i find most users are more conserned with speed then their memory usage.

If anyone has a more efficent way of pulling back the data i would be very greatful if u could get back to me

thanks

 

[Turkbear]

Hi,
If your users want to access a subset of a certain number of rows based on rownum( remember that this is a meaningless condition) the method I posted is the ONLY way to do that for some arbitrary subset of rows between 2 rownums
If the values of the rows ( not their rownum) is important then use a where clause to subset the data that is not dependent on the pseudo-variable rownum ( it is a meaningless number wheen it comes to Identifying data).

rownum just counts the number of rows returned by the query AFTER is gets them..

 

[guts]

Hi guys..

I am little new to these forums.
I have a table containing thousands of rows but I only want user to restrict to first 500 rows based on selection criterion. Here is what I am doing currently.

select * from table_name where ... rownum < 500 order by ...

but this thing is not returning same number of rows everytime i mean for same where clause its returning different set of rows.

can anybody tell me a better and correct way to do it??

guts

 

[karluk]

Spenglerr's suggestion above is the standard way to force Oracle to return the same set of rows every time.

select * from (select ... from ... where ... order by ...)
where rownum < 500;

The where clause in the nested select imposes a fixed order, and then the &quot;where rownum < 500&quot; in the outer select restricts selection to a fixed number of rows. This method is supported in Oracle 8i and later releases.

It would be nice if Oracle supported a &quot;top 500&quot; syntax, but you can't have everything I guess.

 

[guts]

But in order for this thing to work I will have to index everything in the table because my order by clause spawns almost every column in table.
and above solution is taking excessively long because it first selects all the rows orders them and then gets first 500 from them. Its useless to me. I am using rownum so that I dont force oracle to select more data then I want. but here its selecting everything anyway.

Any suggestions??

guts

 

[Turkbear]

Using rownum is not a way to limit what Oracle selects! As I and others have pointed out, rownum is only used AFTER the rows have been selected based on the rest of the where clause.

To limit data choose a where clause that actually specifies what data you want returned based on the contents of a field or fields and not on a pseudovariable like rownum.
May be time to renew your acquaintance with the Oracle Concepts manual

 

[uparna]

Turkbear,
I agree with what you say , but in the case that we are discussing , erixire and guts both want a certain number of rows to be retured from the main resultset , regardless of the where clause entries ( one use of this would be in applications where the results of a query are shown page by page , each page listing 100 rows .... etc ). So , pls tell us if there is any better way of limiting the number of rows returned , without the where clause being used.Rownum looks to be the way out , but is a very resource and time consuming solution ... any other alternatives ? Regards,
S. Jayaram Uparna .

If the need arises,you are welcome to mail me at upparna@yahoo.com .

 

[Turkbear]

Hi, It does not have to be time consuming if the data is well designed..The following is run against a 10000 row, 150 field table and returns the top 10 salaries in less than 5 seconds:

  1  select * from (select translate(empl_nm,'abcdefghijklmnopqrstuvwxyz','poiuytrewqasdfghjklmnbvcxz'cd  where empl_stat_cd = 'A' order by cmptn_rt desc)
  3* where rownum < 11
SQL> /

CD                                                   CMPTN_RT
-------------------------------------------------- ----------
Last1, First, MI                                         51.93
Last2, First, MI
51.91
Last3, First, MI
51.9
Last4, First, MI
51.89
Last5, First, MI
51.89
Last6, First, MI
51.89
Last7, First, MI
51.89
Last8, First, MI
51.89
Last9, First, MI
51.89
Last10, First, MI
51.89

10 rows selected.

 

[rohanem]

Hi,
I have a solution..see if this works.

select * from TABLE_NAME where COL1_NAME='CONDITION1'
and rowid in(select rowid from TABLE_NAME where COL1_NAME='CONDITION1'
and rownum <11)

The table name and where clause should be same in the query and sub-query..Please let me know if it works ok

Thanks
Rohan

 

[Turkbear]

Hi,
The problem with this is that while 10 records
will be returned, they could be a different 10 each time you run it if the table has been modified by inserts or deletes in the meantime( Oracle's background processes may move the data to some other physical location and the rowid will then change)

 

[Turkbear]

Hi

In my previous post I failed to take into account that a
different set of 10 records could be returned
without a change ( actually regardless of a change) to the rowid...
Oracle does not store records in any determinable order, so unless an order by is included, the rows you get back are
in random order.

 

[rohanem]

Hi Turkbear,
You do have a point..my question is since I am not hard-coding any values and selecting using rowid,it shouldnt be a problem right?..I mean even if the underlying table gets updated,the query will always return the first 10 rows of the original query..thats what is required.irrespective of changes in data right erixire?

 

[Turkbear]

Hi,
No it will not return the first 10 rows, since
there is no such thing in Oracle - it will return 10 random ( sortof ) rows...
Remember, no order by means no predictable order of rows returned.

 

[rohanem]

See you cannot give order by in the sub-query..Giving an order-by in the main query does not make any difference..I have tried out the result a number of times